By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,780 Members | 1,550 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,780 IT Pros & Developers. It's quick & easy.

Count and Group By two fields in a Query

P: 58
How can I Count the number of Customers that a Sales Person had in a City X? The idea is to Group By [Location Name] and Sub Group By [Sales Person], and then Count the rest of the fields. This is the scenario:

[Location Name]……[Sales Person]…….[CountOfCustomers]…..etc

………CITY1
……………………….......Steve K………………………..25
…………………………….John M………………………..15
…………………………...Dianne L……………………….26
……..CITY 2
………………………......Scott P………………………….36
…………………………..Steve N.……..………………….24
…………………………Christian G……………...……….10

So far, I can Count (in this case Customer Name or any other field) if I Group By only one field, for instance:

Code:
SELECT [Sales Table].[Location Name], Count([Sales Table].[Customer Name]) AS [CountOfCustomers], Count([Sales Table].[Appointments]) AS [CountOfAppointments]
FROM [Sales Table]
GROUP BY [Sales Table].[Location Name];

The problem I have is, when I Group By two fields, the Count is 1 when the record is duplicated.
Any help will be greatly appreciated.

Thank you in advance.
plaguna
Oct 16 '08 #1
Share this Question
Share on Google+
1 Reply


P: 93
Hi

Can you paste the SQL code you have problems with and example of the tables fields + records that cause the problem?

Regards
Emil
Oct 17 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.