SQL query to group by status and show count for each status seperately

Question

I have a table

ID  Name   Status      Mode
1   Test1  Processed   Mode A
2   Test2  In-Flight   Mode B
3   Test3  Processed   Mode B
4   Test4  In-Flight   Mode A
5   Test5  Processed   Mode A

Please help to build a query that will return

Mode    Processed In-Flight
Mode A  2         1
Mode B  1         1

Got the answer but now I am in confusion how to execute this query in spring boot app which connects to PostgreSQL I am using crud repository

Answer 1

You can use filtered aggregation for that:

select mode, 
       count(*) filter (where status = 'Processed') as processed,
       count(*) filter (where status = 'In-Flight') as in_flight
from the_table
group by mode
order by mode;