CODEWITHSUNDEEP
sqlpostgresql
Piechartking
Piechartking

Reputation: 135

How to create two columns for sums from the same column in Postgres?

I have a table with the following columns: 

Type | Date | Amount

I used the SUM function to add values in the "Amount" column (as SUM1) when Type has value like 'E'. Here is my query:

select 
  to_char("Date", 'Mon') as mon,
  extract(year from "Date") as yyyy,
  sum("Amount") as SUM1 
from "Transactions"
where "Type" LIKE 'E%' and "Date" between '01/01/2015' and '08/31/2015'
group by 1,2
order by yyyy, mon;

This results in rows like:

mon | yyyy | SUM1

What I would like to do, however, is to also show another sum column (SUM2), to show the sum of adding values from "Amount" where "Type" is like "P", and so show rows like:

mon | yyyy | SUM1 | SUM2

Upvotes: 0

Views: 415

Answers (2)

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31143

Depends on your PostgreSQL version. With older ones you can do a conditional sum with CASE:

select to_char("Date", 'Mon') as mon,
extract(year from "Date") as yyyy,
sum(CASE WHEN "Type" LIKE 'E%' then "Amount" ELSE 0 END) as SUM1,
sum(CASE WHEN "Type" LIKE 'P%' then "Amount" ELSE 0 END) as SUM2
from "Transactions"
where "Date" between '01/01/2015' and '08/31/2015'
group by 1,2
order by yyyy, mon;

Or if you're using PostgreSQL 9.4 or newer you can use the nicer FILTER syntax:

select to_char("Date", 'Mon') as mon,
extract(year from "Date") as yyyy,
sum("Amount") FILTER (WHERE "Type" LIKE 'E%') as SUM1,
sum("Amount") FILTER (WHERE "Type" LIKE 'P%') as SUM2
from "Transactions"
where "Date" between '01/01/2015' and '08/31/2015'
group by 1,2
order by yyyy, mon;

Upvotes: 1

jpw
jpw

Reputation: 44871

You can use a case expression as argument to the sum function to choose what gets summed up like this:

select 
    to_char("Date", 'Mon') as mon,
    extract(year from "Date") as yyyy,
    sum(case when "Type" LIKE 'E%' then "Amount" else 0 end) as SUM1,
    sum(case when "Type" LIKE 'P%' then "Amount" else 0 end) as SUM2
from "Transactions"
where "Date" between '01/01/2015' and '08/31/2015'
  and "Type" like 'E%' or "Type" like 'P%'
group by 1,2
order by yyyy, mon;

Upvotes: 1

Related Questions