统计包含特定值的记录总数

Question

我有一个问题，希望你们能够帮助我。

我有一张表格，包含两列：

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

我需要创建一个视图，返回以下这样的值：

TYPE:RAID     ACCEPTED:5          DENIED:7

我想知道TYPE中有多少个值是“Raid”，以及其中有多少个值是“Accepted”和“Denied”。

先行感谢！！

- user1311030

Answer 1

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

- Philip Kelley

Answer 2

您可以将 COUNT 与 CASE 语句结合使用。

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 …) 也是可行的，但你需要在 ELSE 子句中返回 0 而不是 NULL。

- knittl

---

这样做不起作用。CASE WHEN需要一个END来完成该块。 - Magisch

@Magisch: 是的，你说得对。谢谢你指出来，我已经修复了代码片段！ - knittl

Answer 3

这段代码应该适用于mySQL。

SELECT type, COUNT(*)
FROM table
GROUP BY type;

或者

SELECT type, authorization, COUNT(*)
FROM table
GROUP BY type, authorization;

- Simon Forsberg

Answer 4

select count(*) as count from tbl_name where type='Raid'

获取所有类型为raid的总数

您是指像这样的内容吗？

- sujal

Answer 5

嘿，这可能有所帮助：

select type as 'TYPE',sum(Denied) as 'DENIED',sum(Accepted) as 'AUTHORIZED' from
(
 SELECT type,0 as 'Denied',count(*) as 'Accepted' from t where authorization = 'Accepted'    group by type
 union all
 SELECT type,count(*) as 'Denied',0 as 'Accepted' from t where authorization = 'Denied'     group by type ) as sub_tab group by TYPE;

- Pruthvithej

---

干草是给马吃的。 - rghome