Quick query question! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Quick query question!

Author

Topic

thesaintjim
Starting Member

3 Posts

Posted - 2004-11-29 : 17:36:28

SELECT GOLDMINE.CNAME, GOLDMINE.PHONE
FROM GOLDMINE
WHERE GOLDMINE.PHONE = 7246541112 AND GOLDMINE.SEX="M" AND GOLDMINE.DEPT =
"MARKETING"
ORDER BY GOLDMINE.SNAME DESC;

I am trying to make this sql query retrieve and print the customer names from the goldmine table in access where the phone matches to 7246541112 and count the
number of all the male customers who match department MARKETING. I just cant figure out how to get it to count how many records it matches when it finds a phone # and department that matches what I said? Any ideas?! Thanks!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-29 : 18:12:49

The query can't make coffee ant tea at the same time. ( at least not without tweaking )
run:
SELECT COUNT(*)
FROM ...
WHERE ...
to get the count of records matching the where conditions.

rockmoose

thesaintjim
Starting Member

3 Posts

Posted - 2004-11-29 : 18:16:03

Is there anyway I can make it display the names and have it count the number of record it matches based off the where clause at the same time?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-29 : 18:22:51

I think what you are looking for is GROUP BY. But please provide an example to illustrate what you want. Show us the data and then the expected result set.

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-29 : 18:24:19

Is this what you are after ?

SELECT GOLDMINE.CNAME, GOLDMINE.PHONE , COUNT(*)
FROM GOLDMINE
WHERE GOLDMINE.PHONE = 7246541112 AND GOLDMINE.SEX="M" AND GOLDMINE.DEPT =
"MARKETING"
GROUP BY GOLDMINE.CNAME, GOLDMINE.PHONE
ORDER BY GOLDMINE.CNAME DESC;

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-29 : 18:27:22

BTW, What is the Primary Key on the GOLDMINE table ?
A PK will prevent duplicates, if that is a problem.

rockmoose

thesaintjim
Starting Member

3 Posts

Posted - 2004-11-29 : 18:31:05

Ah, the group by clause. Forgot about that. Sir, I thank you!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-29 : 18:50:10

Actually it's Miss Tara!,
I only remembered the GROUP BY after she suggested it