Mysql how to count column values and create corresponding column names-CodePudding

Mysql Version mysql Ver 8.0.29-0

Inital table

tableA

name	uid	count
first	a	1
first	b	2
first	c	1
first	d	2
second	e	1
second	f	2
second	g	1
second	h	1

tableB

uid	type	other_columns
a	1
b	1
c	2
d	2
e	2
f	3
g	3
h	3

try

SELECT DISTINCT a.name, b.type, SUM(a.count)
FROM (
    SELECT `name`, `uid`, `count`
    FROM tableA
) a
INNER JOIN tableB b
ON a.uid = b.uid
GROUP BY a.name, b.type

get

name	type	count
first	1	3
first	2	3
second	2	1
second	3	4

expect

address	count1	ratio1	count2	ratio2	count3	ratio3	total
first	3	0.5	3	0.5	0	0	6
first	0	0	1	0.2	4	0.8	5

how can I make it?

Thank you

CodePudding user response：

First create a column for each type, by having a condition that gives other types the value of 0 and your desired type the actual value

select 
name, 
sum(if(type = 1, count, 0)) count1,
sum(if(type = 2, count, 0)) count2,
sum(if(type = 3, count, 0)) count3,
sum(count) total
from t
group by name;

For the ratios, you have 2 options,

you can surround the query with another query to calculate the ratios:

select 
name, 
count1, count1/total as ratio1,
count2, count2/total as ratio2,
count3, count3/total as ratio3,
total
from (select 
name, 
sum(if(type = 1, count, 0)) count1,
sum(if(type = 2, count, 0)) count2,
sum(if(type = 3, count, 0)) count3,
sum(count) total
from t
group by name) t;

Calculate the ratios directly in the first query:

select 
name, 
sum(if(type = 1, count, 0)) count1,
sum(if(type = 1, count, 0)) / sum(count) ratio1,
sum(if(type = 2, count, 0)) count2,
sum(if(type = 2, count, 0)) / sum(count) ratio2,
sum(if(type = 3, count, 0)) count3,
sum(if(type = 3, count, 0)) / sum(count) ratio3,
sum(count) total
from t
group by name;

Please note, that I didn't take into consideration the case that your count field has only 0 values, which will lead to a total of 0 and dividing by 0
I don't know if it's possible in your case or not, but if it does, please add another condition checking the total value before calculating the ratios

DB Fiddle that shows the above:
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3a98530b3d75c0d2938198ad89397c24