MySQL tutorial: mysql group by count percentage

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

mysql concat() function