CODEWITHSUNDEEP
mysqlsql
Nikaple
Nikaple

Reputation: 121

How to calculate percentage by count of column values

Given table like this.

+----+-------+-----+
| id | class | tag |
+----+-------+-----+
|  1 |     1 | aaa |
|  2 |     1 | abb |
|  3 |     1 | acc |
|  4 |     1 | baa |
|  5 |     2 | bbb |
|  6 |     2 | aaa |
+----+-------+-----+

How can I calculate count of tags starts with a within the same class for every class in the table? The result of above table will be:

+-------+---------+
| class | percent |
+-------+---------+
|     1 |  0.7500 |
|     2 |  0.5000 |
+-------+---------+

PS: I've already had a solution, but the table is big(several millions of rows), could you optimize this?

SELECT class, (CAST(cnt_1 AS DECIMAL) / cnt_2) AS percent 
FROM (
  SELECT class, count(tag) AS cnt_1 FROM (
    SELECT class, tag FROM t_test 
    WHERE tag LIKE 'a%'
  ) AS tmp1 
  GROUP BY class
) AS tmp2 
NATURAL JOIN (
  SELECT class, count(tag) AS cnt_2 
  FROM t_test GROUP BY class
) AS tmp3;

Upvotes: 3

Views: 101

Answers (2)

Rajat
Rajat

Reputation: 5803

Update: Accidentally, very similar to Gordon's answer. I am just adding an edit to make 'percent' look like percentages

select class, round((avg(tag like 'a%')*100),2) percent
from t_test
group by class;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I would use conditional aggregation:

select class, avg( tag like 'a%' ) as a_ratio
from t_test
group by class;

Percents generally go from 0 to 100. ratio is a better name for your column.

Here is a db<>fiddle illustrating that this works.

Upvotes: 1

Related Questions