CODEWITHSUNDEEP
sqlt-sqlgroup-bycase
StayPuft
StayPuft

Reputation: 135

GROUP BY shows the same group more than once when using CASE

I'm having an issue with a CASE Statement in T-SQL

Here is the query:

Select 
 CASE WHEN cri.ChartRetrievalMethodID IS NULL THEN wfseg.SiteEventGroupID
 ELSE cri.ChartRetrievalMethodID END as Type,
 count(distinct c.chartid) TotalCharts 
From Sites s LEFT JOIN Charts c ON s.SiteID=c.SiteID 
LEFT JOIN ChartRetrievalInformation cri ON c.ChartID=cri.ChartID 
LEFT JOIN WFSiteEvents wfse ON wfse.SiteID=s.siteid 
LEFT JOIN WFSiteEventTypes wfset ON wfset.EventTypeID=wfse.EventTypeID 
LEFT JOIN WFSiteEventGroups wfseg ON wfset.SiteEventGroupID=wfseg.SiteEventGroupID
Where 
 wfse.EventStatusID in (1,2)
 and s.ProjectID=110
group by 
 cri.ChartRetrievalMethodID, wfseg.SiteEventGroupID

I'm getting a lot of multiple rows instead of them combining into one - example: 

+------+--------------+
| Type | Total Charts |
+------+--------------+
| 3    | 28           |
| 3    | 3            |
+------+--------------+

Ideally I would like these two rows mashed together to be just one:

+------+--------------+
| Type | Total Charts |
+------+--------------+
| 3    | 31           |
+------+--------------+

I'm sure there is nothing I'm writing incorrectly but I can't seem to see what it is.

Upvotes: 0

Views: 57

Answers (1)

Air
Air

Reputation: 8615

If you include the fields cri.ChartRetrievalMethodID, wfseg.SiteEventGroupID in the column list for your select statement, it will become clear to you why these are shown in multiple rows with that grouping.

What you want to do is group by the value you're calling Type. In another DBMS this would be as simple as GROUP BY Type, but in SQL Server you must repeat the full expression in the GROUP BY clause.

Upvotes: 1

Related Questions