Unique totals query - SOLUTION

[Michael Abrams]

This code :

Code: Select all

SELECT GROUP.[Employer Name], Count([WEEKLY AUDIT COUNT(04-14)].RECNUM) AS CountOfRECNUM
FROM [WEEKLY AUDIT COUNT(04-14)] LEFT JOIN [GROUP] ON ([WEEKLY AUDIT COUNT(04-14)].BENEFIT_PLAN = GROUP.[Medical Benefit Plan ID]) AND ([WEEKLY AUDIT COUNT(04-14)].EMPLOYER_GROUP = GROUP.[GROUP#])
GROUP BY GROUP.[Employer Name], [WEEKLY AUDIT COUNT(04-14)].EMPLOYER_GROUP, [WEEKLY AUDIT COUNT(04-14)].BENEFIT_PLAN
ORDER BY GROUP.[Employer Name];

produces this:

Employer Count
ABC INC 4
XYZ INC 14
XYZ INC 2
XYZ INC 2
XYZ INC 11
DEF INC 3
EFG INC 1
GHI INC 1
WXY INC 3
WXY INC 6
WXY INC 4

I would like this:

Employer Count
ABC INC 4
XYZ INC 29
DEF INC 3
EFG INC 1
GHI INC 1
WXY INC 13

What do I need to modify in the code to obtain desired result?

Thank you.

Michael

[HansV]

Change the GROUP BY clause to

GROUP BY GROUP.[Employer Name]

You can omit the ORDER BY clause - the query will automatically be sorted by Employer Name.

[Michael Abrams]

Thank you Hans - perfect as always.

Michael