YAM YAM - 3 months ago 13
SQL Question

Arrange select query in postgresql?

I have a query :

select channel, status, sum(quantity::integer)
from sale group by channel,status;


this is giving the following output:

channel status quantity
Arham Return 1801
Arham DISPATCHED 49934
Arham CANCELLED 1791
Arham DELIVERED 22


But I want this output like:

channel return DISPATCHED CANCELLED DELIVERED
Arham 1801 49934 1791 22


Is it possible in postgresql?
If yes then how ?

Answer

Exploit Boolean to integer conversion giving either 0 or 1, then multiply by that:

select channel
     , sum((status = 'Return') :: int * quantity :: int) as return
     , sum((status = 'DISPATCHED') :: int * quantity :: int) as DISPATCHED
     , sum((status = 'CANCELLED') :: int * quantity :: int) as CANCELLED
     , sum((status = 'DELIVERED') :: int * quantity :: int) as DELIVERED
from sale
group by channel

An equivalent solution is using case/when/then, for example:

sum(case when status = 'Return' then quantity :: int else 0 end)