scgibson Posted November 4, 2011 Share Posted November 4, 2011 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 More sharing options...
justsomeguy Posted November 4, 2011 Share Posted November 4, 2011 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 More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.