I am trying to write a simple SQL code that can get the desired output, from this table -
我正在尝试编写一个简单的SQL代码，可以从此表中获得所需的输出 -
Original Table :
原表：
id | type
123 0
123 1
123 1
345 0
345 0
What I'm trying to get is:
我想要得到的是：
id | zeroCount | oneCount
123 1 2
345 2 0
I tried to group by the id and also type but, both of them gives the same thing!
我尝试按ID进行分组并键入，但是，它们都给出了同样的东西！
How to get desired output?
如何获得理想的输出？
2 个解决方案
#1
4
Here is a simple way, assuming the values are only 0 and 1:
这是一种简单的方法，假设值只有0和1：
select id, sum(1 - type) as zerocount, sum(type) as onecount
from t
group by id;
A more typical approach would use case (or even pivot):
更典型的方法是使用case（甚至pivot）：
select id, sum(case when type = 0 then 1 else 0 end) as zerocount,
sum(case when type = 1 then 1 else 0 end) as onecount
from t
group by id;
#2
0
You could get fancy and use a pivot function. But this will work:
你可以想象并使用枢轴功能。但这会奏效：
Select id,
sum(case when type = 0 then 1 else 0 end) as ZeroCount,
Sum(case when type = 1 then 1 else 0 end) as OneCount
from table
group by id
order by id
#1
4
Here is a simple way, assuming the values are only 0 and 1:
这是一种简单的方法，假设值只有0和1：
select id, sum(1 - type) as zerocount, sum(type) as onecount
from t
group by id;
A more typical approach would use case (or even pivot):
更典型的方法是使用case（甚至pivot）：
select id, sum(case when type = 0 then 1 else 0 end) as zerocount,
sum(case when type = 1 then 1 else 0 end) as onecount
from t
group by id;
#2
0
You could get fancy and use a pivot function. But this will work:
你可以想象并使用枢轴功能。但这会奏效：
Select id,
sum(case when type = 0 then 1 else 0 end) as ZeroCount,
Sum(case when type = 1 then 1 else 0 end) as OneCount
from table
group by id
order by id