Count the total records containing specific values

Question

I have a question and hope you guys can assist me.

I have a table containing two columns:

type           // contains 2 different values: "Raid" and "Hold"
authorization  // contains 2 different values: "Accepted" or "Denied"

I need to make a view that returns values like this:

TYPE:RAID     ACCEPTED:5          DENIED:7

Basically I want to know how many of the values in TYPE are "Raid" and then how many of them are "Accepted" and "Denied".

Thank you in advance!!

Answer 1

You can use COUNT in combination with a CASE statement

SELECT COUNT(CASE authorization WHEN 'denied' THEN 1 ELSE NULL END) as denied,
  COUNT(CASE authorization WHEN 'authorized' THEN 1 ELSE NULL END) as authorized
FROM table
WHERE type = 'RAID'

SUM(CASE …) is also possible, but you'll have to return 0 in the ELSE clause instead of NULL

Answer 2

SELECT
   Type
  ,sum(case Authorization when 'Accepted' then 1 else 0 end) Accepted
  ,sum(case Authorization when 'Denied' then 1 else 0 end) Denied
 from MyTable
 where Type = 'RAID'
 group by Type