Pallavi Pallavi - 26 days ago 11
SQL Question

How to save the switch cases results in different columns

I have the following query currently:

SELECT name, id,
CASE
WHEN( status = 'Missing' AND severity = 'Optional' ) AND ( id=123 )
THEN COALESCE(count(patchid),0)
ELSE 0
END AS missingoptional,

CASE
WHEN( status = 'Missing' AND severity = 'Important' ) AND ( id=123 )
THEN COALESCE(count(patchid),0)
ELSE 0
END as missingimportant

FROM tablename
GROUP BY name, id, status, severity
ORDER BY id


Current result:

name id missingoptional missingimportant
abc 123 10 0
abc 123 0 20


Expected result:

name id missingoptional missingimportant
abc 123 10 20


Please let me know what changes do I have to do in the above query to get the expected output.

Answer

Perhaps this is what you want:

SELECT name, id, 
  SUM(CASE 
      WHEN( status = 'Missing' AND severity = 'Optional' ) AND ( id=123 ) 
          THEN 1
      ELSE 0
      END) AS missingoptional,
  SUM(CASE 
      WHEN( status = 'Missing' AND severity = 'Important' ) AND ( id=123 ) 
          THEN 1
      ELSE 0
      END) as missingimportant
    FROM tablename
GROUP BY name, id
ORDER BY id

I removed status and severity from the GROUP BY, and instead put SUM() in the select-list to do aggregation.

Comments