SQL 计算包含特定值的总记录数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9998220/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Count the total records containing specific values
提问by user1311030
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 TYPEare "Raid" and then how many of
them are "Accepted" and "Denied".
基本上我想知道有多少值TYPE是“Raid”，然后有多少是“Accepted”和“Denied”。
Thank you in advance!!
先感谢您！！
回答by Philip Kelley
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
回答by Simon Forsberg
This code should work for mySQL
此代码应该适用于 mySQL
SELECT type, COUNT(*)
FROM table
GROUP BY type;
or
或者
SELECT type, authorization, COUNT(*)
FROM table
GROUP BY type, authorization;
回答by knittl
You can use COUNTin combination with a CASEstatement
您可以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 …)is also possible, but you'll have to return 0in the ELSEclause instead of NULL
SUM(CASE …)也是可能的，但你必须0在ELSE子句中返回而不是NULL
回答by sujal
select count(*) as count from tbl_name where type='Raid'
for total number of type=raid
类型总数=raid
Are you saying something like this?
你是在说这样的话吗？
回答by Pruthvithej
Hey this might help:-
嘿，这可能有帮助：-
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;
