get total count of each type in a column-CodePudding

I am doing a jasper report to count the statistic of the fruits choices.

I have 2 table, Fruit, Fruit_choices

Table Fruit :

id	fruit
0	apple
1	banana
2	orange

Table Fruit_choices ：

id	choice_1	choice_2	choice_3
0	0	1	2
1	0	2	1
2	2	0	1

I want to know the sum of the user that select the fruits in choice_1,choice_2,choice_3

My ideal output is like below

fruit	c1Count	c2Count	c3Count
apple	2	1	0
banana	0	1	2
orange	1	1	1

CodePudding user response：

If you have only 3 choice, you can get the result with combination of UNION and Aggregation.

If your number of choices increase, then the query would be more complicated and expensive

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&fiddle=07dd3ca27d231cfcdfbcbefff7f140f6

Explanation:

We can separate our data for each choice. For example, for CHOICE_1, we can group by it and get count of only CHOICE_1. We have to hardcode 0 for other counts.

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

This will give you below result.

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

Similarly union for other 2 choices to get below result

 ------- --------- --------- --------- 
| 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 |
 ------- --------- --------- ---------

Now you can group by FRUIT and use SUM to get count of each FRUIT. Then join the final result with FRUIT table to get name of the FRUIT.

CodePudding user response：

I'm using conditional aggregation. No GROUP BY clause is required in this case. If you're interested, try it.

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)
;