Group query by join

Question

I have two tables in my database:

1. applications (id, type, user_id, status_id).
2. statuses (id, name);

I want to group by type to get count of each type and then as third column I want to get how many of that count has status_id of 3

something like this:

type	type_count	status_3_count
type1	4	2
type2	10	5

I tried this but it doesn't work.

select a.type, count(*) type_count, count(b.id) status_3_count
    from applications a
    join statuses b on b.id = a.status_id and b.id = 3
    group by a.type, b.id

Answer 1

You can get all those information with group by from applications table. Please try below query:

select type,count(*) type_count,sum(case when status_id=3 then 1 else 0 end)status_3_count
from applications 
group by type

Answer 2

You can use conditional aggregation as follows:

select a.type, count(*) type_count, 
       count(case when status = 3 then 1 end) status_3_count
    from applications a
    join statuses b on b.id = a.status_id and b.id = 3
Group by a.type