获取列中每种类型的总数

Question

我正在做一份关于水果选择统计的jasper报告。

我有2个表，水果，水果选择

<<p>表strong>水果:

<表类>id水果tbody><<tr>0苹果1香蕉2橙色

如果您只有3个选择，您可以使用UNION和Aggregation的组合来获得结果。

如果您的选择数量增加，那么查询将更加复杂和昂贵

SELECT f.FRUIT,SUM(C1COUNT),SUM(C2COUNT),SUM(C3COUNT)
FROM 
FRUIT f 
JOIN
(
SELECT  CHOICE_1 AS FRUIT ,COUNT(1) AS C1COUNT, 0 AS C2COUNT, 0 AS C3COUNT
FROM FRUIT_CHOICES 
GROUP BY CHOICE_1
UNION

SELECT  CHOICE_2 AS FRUIT,0 AS C1COUNT, COUNT(1) AS C2COUNT, 0 AS C3COUNT
FROM FRUIT_CHOICES
GROUP BY CHOICE_2
UNION

SELECT  CHOICE_3 AS FRUIT,0 AS C1COUNT, 0 AS C2COUNT, COUNT(1) AS C3COUNT
FROM FRUIT_CHOICES
GROUP BY CHOICE_3
) c 
ON f.ID=c.FRUIT
GROUP BY c.FRUIT

https://dbfiddle.uk/?rdbms=mysql_5.5&小提琴= 07 dd3ca27d231cfcdfbcbefff7f140f6

解释:

我们可以为每个选择分离数据。例如，对于CHOICE_1，我们可以根据它分组并获得仅CHOICE_1的计数。对于其他计数，我们必须硬编码0。

SELECT  CHOICE_1 AS FRUIT ,COUNT(1) AS C1COUNT, 0 AS C2COUNT, 0 AS C3COUNT
FROM FRUIT_CHOICES 
GROUP BY CHOICE_1

结果如下:

+-------+---------+---------+---------+
| FRUIT | C1COUNT | C2COUNT | C3COUNT |
+-------+---------+---------+---------+
| F0    |       2 |       0 |       0 |
| F2    |       1 |       0 |       0 |
+-------+---------+---------+---------+

对其他两个选项进行类似的并集，得到低于结果

+-------+---------+---------+---------+
| FRUIT | C1COUNT | C2COUNT | C3COUNT |
+-------+---------+---------+---------+
| F0    |       2 |       0 |       0 |
| F2    |       1 |       0 |       0 |
| F0    |       0 |       1 |       0 |
| F1    |       0 |       1 |       0 |
| F2    |       0 |       1 |       0 |
| F1    |       0 |       0 |       1 |
| F2    |       0 |       0 |       1 |
+-------+---------+---------+---------+

现在可以按FRUIT分组，并使用SUM来获得每个FRUIT的计数。然后将最终结果与FRUIT表连接以获得FRUIT的名称。

我使用条件聚合。在这种情况下不需要GROUP BY条款。如果你感兴趣，可以试试。

select fruit,c1Count,c2Count,c3Count
from Fruit t1 
join
(select 0 as id,
sum(case choice_1 when 0 then 1 else 0 end) as c1Count,
sum(case choice_2 when 0 then 1 else 0 end) as c2Count,
sum(case choice_3 when 0 then 1 else 0 end) as c3Count
from Fruit_choices
union
select 1 ,
sum(case choice_1 when 1 then 1 else 0 end) ,
sum(case choice_2 when 1 then 1 else 0 end) ,
sum(case choice_3 when 1 then 1 else 0 end)  
from Fruit_choices
union
select 2 ,
sum(case choice_1 when 2 then 1 else 0 end) ,
sum(case choice_2 when 2 then 1 else 0 end) ,
sum(case choice_3 when 2 then 1 else 0 end) 
from Fruit_choices
) t2
using(id)
;