SQL Countif function

Question

I have a table like below

Type of Station | Broadcast Management
----------------+-------------------------
Full Power      | Sinclair Broadcast Group
Full Power      | Sinclair Broadcast Group
LPTV cable      | Sinclair Broadcast Group
LPTV no cable   | Sinclair Broadcast Group

Now I want to execute a query that will bring result looks like below

Broadcast Management       | Full Power | LPTV cable | LPTV no cable
---------------------------+------------+------------+--------------
Sinclair Broadcast Group   |  2         |     1      |  1

Can anyone please help me how to write this query

score 23 · Answer 1 · answered Jun 29 '13 at 17:55

23

There is no single SUMIF or COUNTIF.

But you do have SUMor COUNT and an IF using CASE...

SELECT
  [Broadcast Management],
  SUM(CASE WHEN [Type of Station] = 'Full Power'    THEN 1 ELSE 0 END)   AS [Full Power],
  SUM(CASE WHEN [Type of Station] = 'LPTV Cable'    THEN 1 ELSE 0 END)   AS [LPTV Cable],
  SUM(CASE WHEN [Type of Station] = 'LPTV No Cable' THEN 1 ELSE 0 END)   AS [LPTV No Cable]
FROM
  yourTable
GROUP BY
  [Broadcast Management]

For counts, you can make the ELSE return NULL as the count of 1, 2, 4, NULL is 3.

answered Jun 29 '13 at 17:55

MatBailie

83,401
18
103
137

excellent. thanks @ MatBailie – Arfater Rahman Jun 29 '13 at 18:14
And instead of CASE ... ELSE NULL END, you can just omit the ELSE NULL, since CASE returns NULL anyways if there's no ELSE. – Reversed Engineer Dec 15 '20 at 12:22

SQL Countif function

1 Answers1