Separate rows by group and their percentage of total

Question

Separate rows by group and their percentage of total

Navigation

#1 by (3 votes)

2

I need to get the top 10 results in my table and their respective percentages, separating by groups. For example the table:

+-----+-----+
|id   |fruit|
+-----+-----+
|  1  | or  |
|  2  | ban |
|  3  | or  |
|  4  | or  |
+-----+-----+

It would have to return something like:

+-----+-----+-----+
|COUNT|fruit|PERCE|
+-----+-----+-----+
|  3  | or  | 90% |
|  1  | ban | 10% |
+-----+-----+-----+

90% why it represents 90% of all rows and so goes

mysql sql

asked by anonymous 26.12.2014 / 23:40

1 answer

Reload image in Chrome Why is my app not compatible with some devices on Google play?

score 3 · Accepted Answer

You need to count all, group by fruit and sort by counting each group, limiting by 10. Thus:

select count(id) as contagem, fruit, 
    concat(format((count(fruit) * 100 / (select count(*) from tabela)), 0), '%') as percentagem
from tabela
group by fruit desc
limit 10