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

Question

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 ?

Giorgos Betsos · Accepted Answer · 2016-04-22 08:50:54Z

1

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

edited Apr 22, 2016 at 8:50

answered Apr 22, 2016 at 7:57

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jeremy Over a year ago

Well done, thanks. Why not let id_company in the where clause ? it works too.

Giorgos Betsos Over a year ago

@Jeremy Yes you are right. The id_company predicate should also be used in the WHERE clause because it makes the query run faster.

splash58 Over a year ago

why you save 'id_company=2` in cases while adding where ?

Jeremy Over a year ago

@spash58 i remove it in my query, it's indeed not necessary

Giorgos Betsos Over a year ago

@splash58 OK, I've edit my query to make it cleaner.

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related