Jeremy Jeremy - 7 months ago 9
SQL Question

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 :


  • number lines

  • number lines with state=done

  • sum rating

  • sum rating with state=done



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 ?

Answer

You can try something like this:

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