Jump to content

Group By Or Case ?


scgibson

Recommended Posts

I need some help being a newbie to this game...Running SQL 2008 on W2003. Source table contains the following fields.Name Department Status---- ---------- ------Stuart IT 1Kate IT 4Marc Finance 8Mick Quality 6Stuart IT 8 Report needs to return the count of the status field for each department depending on the status range of numbers, so... status 1 to 8 = 'submitted'status 7 to 8 = 'realised'Thus the report of the above table should look like: Department Submitted Realised---------- --------- --------IT 3 1Finance 1 1Quality 1 0 Using a GROUP BY I can get half of the report but not all of it.Any suggestions would be appreciated. Cheers,Stu

Link to comment
Share on other sites

I spent all this time researching alternatives since MySQL doesn't support a full outer join and came up with this:

SELECT t3.department, SUM(t3.submitted), SUM(t3.realized) FROM (  (SELECT department, COUNT(status) AS submitted, 0 AS realized FROM table GROUP BY department HAVING COUNT(status) >= 1 AND COUNT(status) <= 8) AS t1  UNION  (SELECT department, COUNT(status) AS realized, 0 AS submitted FROM table GROUP BY department HAVING COUNT(status) >= 7 AND COUNT(status) <= 8) AS t2) AS t3 GROUP BY department

Then I realized you're using SQL Server 2008, which does. So this should work:

SELECT t1.department, t1.submitted, t2.realized FROM(SELECT department, COUNT(status) AS submitted FROM table GROUP BY department HAVING COUNT(status) >= 1 AND COUNT(status) <= 8) AS t1FULL OUTER JOIN(SELECT department, COUNT(status) AS realized FROM table GROUP BY department HAVING COUNT(status) >= 7 AND COUNT(status) <= 8) AS t2ON t1.department=t2.department

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...