Percentage of the Total between groups in mySQL

user_14335218 0
Viewed 52

I have the below records in my table

ID Name Price
1  XYZ  100
2  XYZ  100
3  AAA  100
4  AAA  100
5  ABC  100
6  ABD  100

I would like to group single row entries into one group and multi-row entries into another group. The Output will have only 2 rows- Single Entry and Multiple Entry and Percentage of the Price both Categories hold when compared with Total.

ABC and ABD are single Entries as they have only 1 row and XYZ and AAA are multiple entries as they have 2 rows. I would like to sum all Price columns of multiple entries and Single entries and then calculate the percentage

Output:

Category         Percentage
Single_Entry     33.3%                ABC and ABD: (200/600*100)
Multiple_Entries 66.7%                XYZ and AAA: (400/600*100)

How to achieve this in mySQL.

window-functions count sum mysql sql
2 Answers

I view this as two levels of aggregation:

select (case when cnt = 1 then 'single' else 'multiple' end) as entry_type,
       sum(price) / sum(sum(price)) over() as ratio
from (select category, count(*) as cnt, sum(price) as price
      from t
      group by category
     ) c
group by entry_type;
user_11440350

You can use two levels of aggregation:

select category, 100 * sum(price) / sum(sum(price)) over() as percentage
from (
    select sum(price) price,
        case when count(*) = 1 then 'single entry' else 'multiple entriers' end as category
    from mytable t
    group by name
) t
group by category
user_106767160
Related