MySQL: count percentage, if percentage is less than 1% then group them and change label

user_12023581 0
Viewed 101

This is the query I am using to get the percentage per country for participants in events:

SELECT count(p.id) as count, round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) as percentage, p.Nationality
FROM participants as p
JOIN participants_events as pe  on p.id = pe.participant_id
GROUP BY p.Nationality

Which returns a table like this:

+-------+------------+-------------+
| count | percentage | Nationality |
+-------+------------+-------------+
|   100 |        0.8 | Albania     |
|   114 |        0.9 | Algeria     |
|   273 |        2.2 | Belgium     |
|   ... |        ... | ...         |
+-------+------------+-------------+

I want to modify the query in order to don't show any rows with percentage less than 1% but summarize their number and add is as 'Other' with the new percentage. So with the new query the results will be:

+-------+------------+-------------+
| count | percentage | Nationality |
+-------+------------+-------------+
|   214 |        1.7 | Other       |
|   273 |        2.2 | Belgium     |
|   ... |        ... | ...         |
+-------+------------+-------------+
mysql sql
3 Answers

Use a subquery:

SELECT (CASE WHEN count < 0.01 * total THEN 'Other'
             ELSE Nationality
        END) as new_Nationality,
       SUM(count),
       ROUND(100 * SUM(count) / total , 1) as percentage
FROM (SELECT p.Nationality,
             count(*) as count,
             SUM(COUNT(*)) OVER () as total
     FROM participants p JOIN
          participants_events pe
          on p.id = pe.participant_id
     GROUP BY p.Nationality
    )  p
GROUP BY new_Nationality, total
ORDER BY 2 DESC;
user_11440350

Try using Having clause: 

SELECT count(p.id) as count
       , round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) as percentage
       , p.Nationality
FROM participants as p
JOIN participants_events as pe  
     on p.id = pe.participant_id
GROUP BY p.Nationality
HAVING round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) > 1

EDIT

For the second part, try to combine the results of the first with those of the sum of percentages less than 1.

SELECT count(p.id) as count
       , round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) as percentage
       , p.Nationality
FROM participants as p
JOIN participants_events as pe  
     on p.id = pe.participant_id
GROUP BY p.Nationality
HAVING round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) > 1

UNION

SELECT SUM(A.count) As count,
       SUM (A.percentage) As percentage,
       'OTHER'
FROM (SELECT count(p.id) as count
       , round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) as percentage
       , p.Nationality
       FROM participants as p
       JOIN participants_events as pe  
       on p.id = pe.participant_id
       GROUP BY p.Nationality
       HAVING round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) <= 1
       ) A
user_121580240

Please check in your initial query to use the same sets:

So modify your initial query from :

SELECT count(p.id) as count, round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) as percentage, p.Nationality
FROM participants as p
JOIN participants_events as pe  on p.id = pe.participant_id
GROUP BY p.Nationality

TO

SELECT count(p.id) as count, round(100*count(p.id)/(SELECT count(p.id) FROM participants as p JOIN participants_events as pe  on p.id = pe.participant_id),1) as percentage, p.Nationality
FROM participants as p
JOIN participants_events as pe  on p.id = pe.participant_id
GROUP BY p.Nationality

So i.signori answer will be :

SELECT count(p.id) as count
       , 100*count(p.id)/(SELECT count(p.id) FROM participants as p JOIN participants_events as pe  
     on p.id = pe.participant_id) as percentage
       , p.Nationality
FROM participants as p
JOIN participants_events as pe  
     on p.id = pe.participant_id
GROUP BY p.Nationality
HAVING 100*count(p.id)/(SELECT count(p.id) FROM participants as p JOIN participants_events as pe  
     on p.id = pe.participant_id) > 1

UNION

SELECT SUM(A.count) As count,
       SUM(A.percentage) As percentage,
       'Other'
FROM (SELECT count(p.id) as count
       , 100*count(p.id)/(SELECT count(p.id) FROM participants as p JOIN participants_events as pe  
     on p.id = pe.participant_id) as percentage
       , p.Nationality
       FROM participants as p
       JOIN participants_events as pe  
       on p.id = pe.participant_id
       GROUP BY p.Nationality
       HAVING 100*count(p.id)/(SELECT count(p.id) FROM participants as p JOIN participants_events as pe  
     on p.id = pe.participant_id) <= 1
       ) A
user_104082550
Related