mysql里分组后统计某一项的SQL

Question

我表示这样的
cate title  type
A     11     1
A     22     0
B     12     1
C     32     1
B     42     0
C     17     1
A     18     0
A     28     0结果是
    count     type=1   type=0
A     4        1        3
B     2        1        1
C     2        2        0

Answer 1

select cate,count(*),
       sum(case when type=1 then 1 else 0 end)[1],
       sum(case when type=0 then 1 else 0 end)[0]
from tb group by cate

Answer 2

select
 cate,
 sum(if(type='1',1,0)) AS [type=1],
 sum(if(type='0',1,0)) AS [type=2],
 count(cate) AS [count]
from
 tb
group by
 cate

Answer 3

select cate,count(*),
sum(case when type=1 then 1 else 0 end) as type1,
sum(case when type=0 then 1 else 0 end) as type0
from tb group by cate

Answer 4

create table tb
(
cate varchar(5),
title int,
type int 
);
insert into tb values 
('a',11,1),
('a',22,0),
('b',12,1),
('c',32,1),
('b',42,0),
('c',17,1),
('a',18,0),
('a',28,0)select
 cate,
 sum(if(type=1,1,0)),
 sum(if(type=0,1,0)), 
 count(cate)
from
 tb
group by
 cate/*a 1 3 4
b 1 1 2
c 2 0 2
*/

Answer 5

mysql> create table tb
(
cate varchar(5),
title int,
type int 
);
insert into tb values 
('a',11,1),
('a',22,0),
('b',12,1),
('c',32,1),
('b',42,0),
('c',17,1),
('a',18,0),
('a',28,0);select
 cate,
 sum(if(type=1,1,0)),
 sum(if(type=0,1,0)), 
 count(cate)
from
 tb
group by
 cate;
Query OK, 8 rows affected
Records: 8  Duplicates: 0  Warnings: 0+------+---------------------+---------------------+-------------+
| cate | sum(if(type=1,1,0)) | sum(if(type=0,1,0)) | count(cate) |
+------+---------------------+---------------------+-------------+
| a    | 2                   | 6                   |           8 |
| b    | 2                   | 2                   |           4 |
| c    | 4                   | 0                   |           4 |
+------+---------------------+---------------------+-------------+
3 rows in set