mysql按类型分组查询语句,如何编写SQL查询以按类型选择和分组以及按类型计数？...

Below is my database table.

id name type

1 a 1

2 a 1

3 a 1

4 a 0

5 a 0

6 b 1

7 b 1

8 b 0

9 b 0

10 b 0

11 c 1

12 c 1

13 c 0

I would like to select and group by type and count per type. How to write the sql query to get the following results?

name count_type_0 count_type_1

a 2 3

b 3 2

c 1 2

解决方案

Just use conditional aggregation:

select name, sum(type = 0) as count_0, sum(type = 1) as count_1

from t

group by name;

This uses a MySQL feature that treats boolean values as numbers in a numeric context, with 0 for false and 1 for true. Hence, adding up the values counts the number of times the expression is true.