CODEWITHSUNDEEP
mysqlsql
Mike Ross
Mike Ross

Reputation: 2972

SQL aggregate function with if condition

I'm wondering if I can perform count function on different status_id with condition when status_id is X then something

This is my current query:

select count(*), status_id
from users
group by status_id

I would like to modify it to have extra condition: when users.status_id is in (18,24,25) then check if users.active = true. In all other cases no need to check the users.active column.

As of now I am doing following

select count(*), status_id
from users
where 1 = case when status_id in (18,24,25) 
           then (case when active = true then 1 else 0 end) 
          else 1 end
group by status_id

Is that the correct way to write query or there is a simpler way?

Thank you

Upvotes: 1

Views: 2394

Answers (2)

ysth
ysth

Reputation: 98398

If you want to include all status ids in the table in the results, even if the special case ones only have false active rows, then you want to do:

select sum(active or status_id not in (18,24,25)), status_id
from users
group by status_id

Upvotes: 0

Oleksii Tambovtsev
Oleksii Tambovtsev

Reputation: 2834

The easiest way is:

SELECT COUNT(*), status_id
FROM users
WHERE status_id NOT IN (18, 24, 25) OR active = true
GROUP BY status_id

Upvotes: 2

Related Questions