Is there a more efficient way to calculate percentage when I got two condictions under GROUP BY?-CodePudding

I got a field "type" which is either "member" or "casual". I got another catagorical field called "duration_type". And I want to calculate the percentage of each catagories in the duration_type, separately for members and casual users. So I run two separate scripts where their only difference is the WHERE type = "member" becomes WHERE type = "casual" .

SELECT 
    type, 
    duration_type,
    CONCAT(ROUND((COUNT(*)/ (SELECT COUNT(*) FROM temp WHERE type ="member")*100 ),2),"%") AS per
FROM temp
WHERE type = "member"
GROUP BY  duration_type;

SELECT 
    type, 
    duration_type,
    CONCAT(ROUND((COUNT(*)/ (SELECT COUNT(*) FROM temp WHERE type ="casual")*100 ),2),"%") AS per
FROM temp
WHERE type = "casual"
GROUP BY  duration_type;

Is there a more compact way to do this in only one command? Simply using GROUP BY type, duration_type is not correct.

CodePudding user response：

You should group by type, duration_type and use SUM() window function to get the total number of rows for each type:

SELECT type, duration_type,
       CONCAT(ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY type), 2), '%') AS per
FROM temp
GROUP BY type, duration_type;

Or, if your version of MySql does not support window functions:

SELECT t1.type, t1.duration_type,
       CONCAT(ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM temp t2 WHERE t2.type = t1.type), 2), '%') AS per
FROM temp t1
GROUP BY t1.type, t1.duration_type;

See a simplified demo.