CODEWITHSUNDEEP
mysqlsql
Jeremy
Jeremy

Reputation: 279

Query count, sum, with condition in one query with mysql

I have this table in a mysql database :

TABLE rating
| id         (int, primary)
| id_company (int, index)
| state      (enum: 'waiting','done','refuse')
| rating     (int between 1 and 5)

I want to get this stats for one id_company :

For this i have 2 queries (exemple with id_company=2) :

SELECT COUNT(1) as `nbr`, SUM(`rating`) as `total` FROM `rating` WHERE `id_company`=2
SELECT COUNT(1) as `nbr`, SUM(`rating`) as `total` FROM `rating` WHERE `id_company`=2 AND `state`='done'

But it is possible to make an unique query to get this stats ?

Upvotes: 2

Views: 43

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can try something like this:

SELECT COUNT(*) as `nbr1`, 
       SUM(`rating`) as `total1`,
       SUM(`state`='done') as `nbr2`, 
       SUM(CASE 
              WHEN `state`='done' THEN `rating` 
              ELSE 0 
           END) as `total2`  
FROM `rating` 
WHERE `id_company`=2

Upvotes: 1

Related Questions