CODEWITHSUNDEEP
mysqlsql
effkay
effkay

Reputation: 854

MySQL Query: count entries by status

I have a log table with following schema:

OperatorId - JobId - Status ( Good/Bad/Ugly )
Alex         6       Good
Alex         7       Good
James        6       Bad

Description: Whenever an operator works on a job, an entry is made along with Status. That's it.

Now I need a report like:

OperatorId - Good Count - Bad Count - Ugly Count
Alex         2            0           0
James        0            1           0

Upvotes: 0

Views: 295

Answers (3)

Andriy M
Andriy M

Reputation: 77657

If, like me, you prefer, whenever possible, to calculate counts with COUNT rather than with SUM, here's an alternative solution, which uses a method asked about in this thread:

SELECT
  operatorid,
  COUNT(status = 'good' OR NULL) as good,
  COUNT(status = 'bad' OR NULL) as bad,
  COUNT(status = 'ugly' OR NULL) as ugly
FROM table
GROUP BY operatorid

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270609

This is called a Pivot Table. It is done by setting a value 1 or 0 for each state and then summing them up:

SELECT 
  T.OperatorId,
  SUM(T.GoodStat) AS Good,
  SUM(T.BadStat) AS Bad,
  SUM(T.UglyStat) AS Ugly
FROM 
(
 SELECT 
   CASE WHEN Status = 'Good' THEN 1 ELSE 0 END AS GoodStat,
   CASE WHEN Status = 'Bad' THEN 1 ELSE 0 END AS BadStat,
   CASE WHEN Status = 'Ugly' THEN 1 ELSE 0 END AS UglyStat,
   OperatorId
  FROM logTable T
)
GROUP BY T.OperatorId

Upvotes: 1

Nicola Cossu
Nicola Cossu

Reputation: 56357

select operatorid,
sum(if(status="good",1,0)) as good,
sum(if(status="bad",1,0)) as bad,
sum(if(status="ugly",1,0)) as ugly
from table
group by operatorid

Upvotes: 2

Related Questions