How to calculate the percentage of values with grouping

Question

I have table:

Country:
id | name     | group| type
1  | USA      | 1    | 0
2  | Germany  | 2    | 1
3  | France   | 2    | 0
4  | China    | 1    | 1
5  | Japan    | 3    | 0
6  | Italy    | 1    | 1
7  | Mexico   | 1    | 0
8  | Columbia | 3    | 0
9  | Taiwan   | 2    | 1
...

Now I would like receive percentange of positive "type" with grouped by "group" field.

So results should looks like:

group| percentage
1    | 50%        -- 0 + 1 + 1 + 0 = 2/4 = 50%
2    | 66%        -- 1 + 0 + 1 = 2/3 = 66%
3    | 0%         -- 0 + 0 = 0/2 = 0%

How is the best way for this? Is this possible in one query? I am newbie in MySQL and I don't have any idea. I can only:

SELECT group, (????) as percentage FROM country GROUP BY group;

Possible duplicate of [Calculating percentages with GROUP BY query](https://stackoverflow.com/questions/6207224/calculating-percentages-with-group-by-query) — Vinay Hegde, Sep 01 '19 at 10:03

score 1 · Answer 1 · edited Sep 01 '19 at 10:50

1

You want AVG

SELECT group, round(avg(type*100.0), 0) as percentage 
FROM country 
GROUP BY group

Fiddle

edited Sep 01 '19 at 10:50

Reimeus

158,255
15
216
276

answered Sep 01 '19 at 10:11

Serg

22,285
5
21
48

score 0 · Accepted Answer · answered Sep 01 '19 at 10:09

If the column type has only the values 0 and 1:

SELECT 
  `group`, 
  round(100.0 * sum(type) / count(*), 0) as percentage 
FROM country 
GROUP BY `group`;

See the demo.
Results:

| group | percentage |
| ----- | ---------- |
| 1     | 50         |
| 2     | 67         |
| 3     | 0          |

How to calculate the percentage of values with grouping

2 Answers2