Getting Percentage and Total from SELECT GROUP BY Mysql

user_3004782 0
Viewed 88

After multiple searches I couldn't find a fit solution that would work in my case.

I have the following table:

id  category    entry_date
1   Suggestion  01/01/2019
2   Suggestion  05/01/2019
3   Compliment  05/01/2019
4   Complaint   12/02/2019
5   Suggestion  09/10/2019
6   Compliment  23/11/2019

I need to show the number of each category and the percentage of each (based on the total entries). The 'where' will limit a date range - but I'm assuming that here it doesn't matter. This is my expected result:

Category       Totals   % of Total Entries
Compliment     2        ˜34%
Complaint      1        16%
Suggestion     3        60%

Here is the query I'm currently using:

SELECT category, 
 COUNT(*) AS total, 
 ROUND(COUNT(category)*100 / (SELECT COUNT(*))) AS pct 
 FROM (Mytable) 
 WHERE `entry_date` >= '2018-01-01' AND `entry_date` <= '2019-12-31' 
 GROUP BY category ORDER BY category ASC

With this the pct is relative to each category (so: 200,100,300), and not the total.

I'm using MySQL 5.7. Thank you all!

mysql
2 Answers

SELECT count can take time, so it will not be fast on big tables

CREATE TABLE Table1
    (`id` int, `category` varchar(10), `entry_date` varchar(10))
;
    
INSERT INTO Table1
    (`id`, `category`, `entry_date`)
VALUES
    (1, 'Suggestion', '01/01/2019'),
    (2, 'Suggestion', '05/01/2019'),
    (3, 'Compliment', '05/01/2019'),
    (4, 'Complaint', '12/02/2019'),
    (5, 'Suggestion', '09/10/2019'),
    (6, 'Compliment', '23/11/2019')
;

SELECT `category`,     COUNT(*) AS cnt,
    100.0 * COUNT(*) / (SELECT COUNT(*) FROM Table1) AS percent   FROM Table1 GROUP BY `category`

category   | cnt |  percent
:--------- | --: | -------:
Suggestion |   3 | 50.00000
Compliment |   2 | 33.33333
Complaint  |   1 | 16.66667

db<>fiddle here

user_51935360

You'd need to do this with window functions in MySQL 8.0:

with cte as (
  select category, count(*) over (partition by category) as subtotal,
    count(*) over () as total from following_table )
select category, subtotal, 
  concat(round(subtotal*100/total), '%') as `% of Total Entries` 
from cte group by category;

Output:

+------------+----------+--------------------+
| category   | subtotal | % of Total Entries |
+------------+----------+--------------------+
| Complaint  |        1 | 17%                |
| Compliment |        2 | 33%                |
| Suggestion |        3 | 50%                |
+------------+----------+--------------------+

In MySQL 5.7, do it with two queries. The first to get the total, then the second to use that result to calculate the percentage per group.

Bill_Karwin_208600
Related