Simple SQL Grouping Question

Question

Simple SQL Grouping Question

Richard McSharry

Ten Centuries

Points: 1249
More actions
June 25, 2009 at 11:14 am

#384847

Hi,
I have a simple query that counts how many type of contact occur for a team in each month. Obviously if there is no activity for a given team in a given month, then that month does not appear in the result set:
SELECT
TeamName,
AppointmentYearMonth,
Count(ClientID)
FROM
dbo.tblActivity
GROUP BY
TeamName,
AppointmentYearMonth
Lets say I now want to break this down by the Individuals with a team, then even more 'holes' appear in the result set, due to not every team member having activity in every month:
SELECT
TeamName,
TeamMemberName,
AppointmentYearMonth,
Count(ClientID)
FROM
dbo.tblActivity
GROUP BY
TeamName,
TeamMemberName,
AppointmentYearMonth
So, my question is this: is there an easy way to build such queries so that the ALL ROWS appear, even where there is no data (ie. showing the counts of zero).
The reason is to put it into a report and to stop DUMB users asking "Why is so-and-so missing from the report?"...and other reasons not even worth debating.
Is the only solution to create a table, pre-populated with zeroes and then update the relevent rows as data is produced?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

To do that you need a calendar table, one that has all the months in it. Then join the tables together (left join) and group.

Edit: And you're probably going to need a table with the team names in it, so that they appear.

Cross join the team table with the calendar table and filter to the limits that you want. Put that in a subquery and then, with a left join, join the activity table to that and group the entire query

SELECT

AllTeamsAllMonths.TeamName,

dbo.tblActivity.YearMonth,

Count(ClientID)

FROM

(dbo.team CROSS JOIN dbo.Calendar) AllTeamsAllMonths LEFT OUTER JOIN dbo.tblActivity ON AllTeamsAllMonths.YearMonth = dbo.tblActivity AppointmentYearMonth AND AllTeamsAllMonths.TeamID = dbo.tblActivity.TeamID

GROUP BY

AllTeamsAllMonths.TeamName,

dbo.tblActivity.YearMonth

Maybe do a google search for calendar tables in SQL. I know they've been written about many times.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 2

If you have a table like "tblTeamMembers" or "tblTeams" you'll need to LEFT JOIN the tblActivity to it.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

terrance.steadman SSCrazy Points: 2097 More actions · Answer 3

Greetings Richard,

Do you have a table for the different teams and for different team members?

If so, then what you could do is this:

SELECT

s1.TeamName,

s1.TeamMemberName,

ISNULL(a.AppointmentYearMonth, 'NA') AS AppointmentYearMonth,

COUNT(a.ClientID) AS ClientCount

FROM

(

SELECT

t1.TeamName,

t2.TeamMemberName

FROM tblTeams t1

JOIN tblTeamMembers t2 ON t1.TeamName = t2.TeamName

) s1

LEFT OUTER JOIN tblActivity a ON s1.TeamName = a.TeamName AND s1.TeamMemberName = a.TeamMemberName

GROUP BY s1.TeamName, s1.TeamMemberName, a.AppointmentYearMonth

I don't know if your TeamNames and TeamMemberNames are in a single table or in 2 different tables, but this code may help you find a solution.

Have a good day.

Terry Steadman

Richard McSharry Ten Centuries Points: 1249 More actions · Answer 4

Thanks all for the replies, I figured it out and could not have done it without you (especially your code example Terry!).

Much appreciated.

😀

Richard