Mysql group by count percentage, you can count the proportion of certain conditions in the total amount of data.
In the following example, we will count the number and proportion of records corresponding to different status field values in the current mysql database table t
.
Test table:
mysql> select * from t;
+----+----------+--------+------+
| id | name | status | age |
+----+----------+--------+------+
| 1 | ylspirit | 1 | 22 |
| 2 | carol | 1 | 21 |
| 3 | apple | 1 | 19 |
| 4 | orange | 2 | 22 |
| 5 | ben | 2 | 23 |
| 6 | john | 3 | 19 |
| 7 | jack | 2 | 18 |
| 8 | han | 1 | 17 |
| 9 | alin | 4 | 25 |
| 10 | tom | 1 | 18 |
+----+----------+--------+------+
10 rows in set (0.00 sec)
SQL:
select
grp.status
, grp.cnt
, round(concat((grp.cnt / tal.cnt) * 100, "%"),2) as percentage
from (
select
status
, count(*) as cnt
from t
group by status
) as grp left join (
select
count(*) as cnt
from t
) as tal on 1=1
;
Results output:
+--------+-----+------------+
| status | cnt | percentage |
+--------+-----+------------+
| 1 | 5 | 50.00 |
| 2 | 3 | 30.00 |
| 3 | 1 | 10.00 |
| 4 | 1 | 10.00 |
+--------+-----+------------+
Reference