CODEWITHSUNDEEP
sqlpostgresqlpivotcrosstab
YAM
YAM

Reputation: 494

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 ?

Upvotes: 0

Views: 111

Answers (3)

user1804599
user1804599

Reputation:

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)

Upvotes: 2

Vivek S.
Vivek S.

Reputation: 21915

Use tablefunc.

First you need to create extension

create extension if not exists tablefunc;

and the query is

SELECT *
FROM   crosstab(
      'select channel::text
             ,status
             ,sum(quantity::integer) 
       from sale group by channel,status')  
AS ct ("channel" text, "Return" int, "DISPATCHED" int, "CANCELLED" int, "DELIVERED" int);

Upvotes: 1

user330315
user330315

Reputation:

If you don't want to use the crosstab function you can do this using filtered aggregates:

select channel,
       sum(quantity) filter (where status = 'Return') as return_amount,
       sum(quantity) filter (where status = 'DISPATCHED') as dispatched,
       sum(quantity) filter (where status = 'CANCELLED') as cancelled,
       sum(quantity) filter (where status = 'DELIVERED') as delivered
from sale
group by channel;

Upvotes: 3

Related Questions