SQL Select for multiple where子句

Question

我正在尝试创建SQL Select，它根据字段返回特定字段的计数。 所以，这是我想要做的。

Select count(distinct id) as TotalCount, -- this will be the total of id 
count(distinct id where type='A') as TotalA, -- this will be total when type='A' 
count(distinct id where type='B') as TotalB -- This will be total when type = 'B' 
from MyTable 

基本上TotalCount = TotalA + TotalB。

如何在SQL Select语句中实现此目的？ 谢谢。

Answer 1

Select count(distinct id) as TotalCount, -- this will be the total of id 
count(distinct case type when 'A' then id else NULL end) as TotalA, 
count(distinct case type when 'B' then id else NULL end) as TotalB 
from MyTable; 

当然TOTALCOUNT可能会或可能不会总量a +共计b，根据实际数据。

Answer 2

你能做到这样的：每个类型

SELECT 
    count(distinct id) as TotalCount, 
    sum(CASE WHEN type = 'A' THEN 1 ELSE 0) as TotalA, 
    sum(CASE WHEN type = 'B' THEN 1 ELSE 0) as TotalB, 
FROM 
    MyTable 

计数：

SELECT 
    type, 
    count(DISTINCT id) 
FROM 
    MyTable 
GROUP BY 
    type 

Answer 3

为什么不简单UNION单独的查询。

Select 'all' as which, count(distinct id) as Total from mytable 
    union 
    select 'a' as which, count(distinct id) where type='A' as Total from mytable 
    union 
    select 'b' as which, count(distinct id) where type='B' as Total from mytable