Counting matching ratings grouped by ID without using table joins? Is it possible? MYSQL?

Question

I have an intermediate table called ratingsPerId that is produced from a stored procedure shown here:

Id   Rating
'1', 'low'
'1', 'low'
'1', 'low'
'1', 'low'
'1', 'low'
'2', 'medium'
'2', 'medium'
'2', 'medium'
'2', 'high'
'2', 'high'
'4', 'high'
'4', 'high'
'4', 'high'
'4', 'high'
'4', 'high'
'9', 'high'
'9', 'high'
'9', 'high'
'9', 'high'
'9', 'high'
'9', 'high'
'9', 'high'
'9', 'high'
'9', 'high'

What I want is for Id 1, count up all the lows, mediums, highs. And do the same for all the following Id 2, 4, 9, etc...

Right now what I have is a table join that counts the Highs and Lows per ID:

drop temporary table if exists t1, t2;
create temporary table t1(ruleid int, high INT);
INSERT into t1(
    SELECT ruleid, count(*) AS High from ratingsPerRuleId  WHERE rating='high' group by ruleid);
create temporary table t2(ruleid int, low int);
INSERT into t2(
    SELECT ruleid, count(*) AS Low from ratingsPerRuleId WHERE rating='low' group by ruleid);

SELECT t1.RuleID, t1.high, t2.low from t1 left join t2 on t1.ruleid=t2.ruleid ;

ID   High  Low
1    NULL   5
2     2    NULL
4     5    NULL
9     9    NULL

As you can see, I will need to create a third table for Medium and do a join of that to get the Medium column.

Is there an easier way to do this in MYSQL? I feel like there is a simple statement that can accomplish this, and my messy table joins of small tables aren't necessary but I don't know enough of MYSQL to get the answer.

I looked into Count(distinct) and Select Distinct but haven't found the answer.

Thanks!

Do you want result like this : for each ID total High, total medium and total low count? — 1000111, Jul 14 '16 at 16:41

1000111 · Accepted Answer · 2016-07-14T16:58:35.013

1

I guess you need to use CASE WHEN expression.

SELECT 
 Id,
 COUNT(CASE WHEN Rating ='high' THEN 1 END) AS high,
 COUNT(CASE WHEN Rating ='medium' THEN 1 END) AS medium,
 COUNT(CASE WHEN Rating ='low' THEN 1 END) AS low
FROM your_table
GROUP BY Id;

Working Demo

OR you can use SUM along with MySQL boolean expression if you don't want to use CASE WHEN.

SELECT 
 Id,
 SUM(Rating ='high') AS high,
 SUM(Rating ='medium') AS medium,
 SUM(Rating ='low') AS low
FROM your_table
GROUP BY Id;

Working Demo

Note: SELECT SUM(a=b) returns 1 only if a=b;

NB: The following sample input is used in the working demos

| Id | Rating |
|----|--------|
|  1 |    low |
|  1 |    low |
|  1 | medium |
|  1 |   high |
|  2 |   high |

edited Jul 14 '16 at 16:58

answered Jul 14 '16 at 16:42

1000111

13,169
2
28
37

Worked like a charm, thank you! I didn't even know about CASE WHEN – truffle Jul 14 '16 at 16:45
1

It's too handy! I would suggest go through a tutorial on `CASE WHEN`. :) – 1000111 Jul 14 '16 at 16:46
1

Also you can take a look at updated answer based on `SUM`. @truffle – 1000111 Jul 14 '16 at 16:52

score 1 · Answer 2 · answered Jul 14 '16 at 16:44

This should work (COUNT excludes null values):

SELECT ruleid
  , COUNT(IF(rating = 'high', 1, NULL)) AS High 
  , COUNT(IF(rating = 'medium', 1, NULL)) AS Medium 
  , COUNT(IF(rating = 'low', 1, NULL)) AS Low 
FROM ratingsPerRuleId 
GROUP BY ruleid
;

Also, if you prefer you can replace COUNT(..., NULL)) with SUM(..., 0)).

Note: 1000111's answer is more portable, as older versions of MS SQL do not support IF(...)

Counting matching ratings grouped by ID without using table joins? Is it possible? MYSQL?

2 Answers2