SQL query for report by name with many columns count by status

Is it possible by sql query to produce a report group by a name with count by status.

Tables:

• Project (id, name, status_id, service_id)
• Status (id, name)
• Service (id, name)

I need to make a report, count by status group by service:

Columns results: Service name, status name 1 , status name 2, status name 3, total

Rows results:

service name 1, 30, 10, 20, 60
service name 2, 10, 5, 40, 55
service name 3, 0, 2, 8, 10

Answer

You seem to want joins and conditional aggregation:

select se.name as service_name,
    sum(case when st.name = 'status 1' then 1 else 0 end) as cnt_status_1,
    sum(case when st.name = 'status 2' then 1 else 0 end) as cnt_status_2,
    sum(case when st.name = 'status 3' then 1 else 0 end) as cnt_status_3,
    count(*) as total
from project p
inner join status  st on st.id = p.status_id
inner join service se on se.id = p.service_id
group by se.id, se.name