Jump to content

Group By Or Case ?


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 post
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 post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...