SQL Countif函数

This question already has answers here:

Sql Server equivalent of a COUNTIF aggregate function

(9个答案)


已关闭6年。




我有一个像下面的 table

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

现在，我想执行一个查询，该查询将带来如下所示的结果

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

谁能帮我怎么写这个查询

最佳答案

没有单个SUMIF或COUNTIF。

但是您确实有SUM或COUNT以及使用IF的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]

对于计数，您可以使ELSE返回NULL，因为1, 2, 4, NULL的计数为3。