slopfront
Reddit, read-only. No JavaScript. No cookies. No tracking.
preferences

[MS SQL] Getting a count before JOIN's

5 points · submitted Jul 2014 by u/michdev

I'm not entirely sure how to phrase my question, which has made finding the answer on SO a bit difficult.

I have a query that prepares a report, grouping by a facility and month, and summarizing flags on related tables. It looks something like:

SELECT dbo.Facilities.FacilityId AS FacilityId, 
    CAST(CAST(YEAR(Incidents.IncidentDate) AS VARCHAR(4)) + RIGHT('0' + CAST(MONTH(Incidents.IncidentDate) AS VARCHAR(2)), 2) + RIGHT('0' + CAST(1 AS VARCHAR(2)), 2) AS DATETIME) AS Date, 
    COUNT(Incidents.IncidentId) AS 'All',
    SUM(CASE dbo.IncidentIncidentTypes.IncidentType WHEN 1 THEN 1 ELSE 0 END) Falls,
            .... 
           additional columns omitted

FROM Incidents 
JOIN dbo.IncidentIncidentTypes ON Incidents.IncidentId = IncidentIncidentTypes.IncidentId
JOIN dbo.IncidentInjuryTypes ON Incidents.IncidentId = IncidentInjuryTypes.IncidentId
JOIN dbo.IncidentTreatments ON Incidents.IncidentId = IncidentTreatments.IncidentId
JOIN dbo.IncidentActionTakens ON Incidents.IncidentId = IncidentActionTakens.IncidentId
JOIN dbo.Facilities ON Incidents.FacilityId = Facilities.FacilityId
GROUP BY Facilities.FacilityId, YEAR(Incidents.IncidentDate), MONTH(Incidents.IncidentDate)

This might be a simple question, but I'm not sure how to get the "top level" count of Incidents (the All column). Instead, it seems to be taking the count of all the rows in my first JOIN. I've tried using DISTINCT with COUNT(DISTINCT Incidents.IncidentId), but that doesn't work either.

Any help is greatly appreciated!

8 comments · 74% upvoted · other discussions

comments

cl0ckt0wer 2 points · Jul 2014

Use a subquery

http://technet.microsoft.com/en-us/library/ms189575%28v=sql.105%29.aspx

[deleted] 2 points · Jul 2014

Instead, it seems to be taking the count of all the rows in my first JOIN

No, its simply counting the IncidentIds within the group. If you use a GROUP BY, then all aggregate functions such as SUM()/COUNT() etc, will count PER group (thats the whole point of a GROUP BY, right?)

For something like SQL Server, it may be as simple as adding WITH ROLLUP after your GROUP BY clause.

Then you will get summary rows for each group and subgroup, and a final 'ALL' row at the end

michdev [OP] 1 points · Jul 2014

This makes a lot of sense, thank you. I was visualizing the JOIN wrong, and thought that the GROUP BY would still have the same number of rows.

thephilski 1 points · Jul 2014

it should pull the data you are wanting for the 'All" just by using Count, but you can try this too?... maybe.. Its not clean, but I can't figure out why just doing a count on it to begin with isn't working for you. I have a feeling its in your group by statement. Maybe your pulling data that has too many distinct values?

sum(case when Incidents.IncidentId is null then 0 else 1 end) as [All]
michdev [OP] 2 points · Jul 2014

Thanks for the suggestion. This ended up being the same as my query, since there's duplicate rows as a result of the JOIN's, it over counts the Incidents.

michdev [OP] 2 points · Jul 2014

It turns out this was the solution after all. The problem was with my JOINs. Since they were an INNER JOIN, any rows without any flags didn't appear, and so doing DISTINCT was giving the wrong result. Changing it to DISTINCT with a LEFT OUTER JOIN gave the correct results. Thanks a lot for the help.

ilikekittens 1 points · Jul 2014

Along the same lines as u/carpii's recommendation - if group by mucks up any other aggregates, try count(incidentids) over (partition by desired_variable).

ajmarks 1 points · Jul 2014

In SQL Server, you have ROLLUP, CUBE, and GROUPING SETS for things just like this: http://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx