CODEWITHSUNDEEP
sqlpostgresql
ZJAY
ZJAY

Reputation: 2807

Simplifying a Sum() across dates

When I run the query below, the temp_08.members table created only features one column (Q4'10) which is the sum of all calcs. This is incorrect.

INSTEAD I am trying to create a separate column for each quarter with the sum(calc) for just data inside that quarter. For some reason the query below does not accomplish this.

Second, is there a more efficient way to write this query. I will have 20 quarters, and the only thing that changes in each is the column name (as Q4_16) and the dates for each select.

SELECT
sum(calc) as Q4_10,
brand
into   temp_08.members
from temp_08.source
WHERE    date > '09/30/2010' and date <= '12/31/2010'
GROUP BY brand

insert into temp_08.members
SELECT
sum(calc) as Q1_11,
brand
from temp_08.source
WHERE    date > '12/31/2010' and date <= '3/31/2011'
GROUP BY brand

insert into temp_08.members
SELECT
sum(calc) as Q2_11,
brand
from temp_08.source
WHERE    date > '3/31/2011' and date <= '6/30/2011'
GROUP BY brand

insert into temp_08.members
SELECT
sum(calc) as Q3_11,
brand
from temp_08.source
WHERE    date > '6/30/2011' and date <= '9/30/2011'
GROUP BY brand

insert into temp_08.members
SELECT
sum(calc) as Q4_11,
brand
from temp_08.source
WHERE    date > '9/30/2011' and date <= '12/31/2011'
GROUP BY brand

Upvotes: 0

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

I am guessing you want something like this:

SELECT date_trunc('quarter', date) as yyyyqq, brand, sum(calc) as sumcals,
INTO temp_08.members
FROM temp_08.source
GROUP BY date_trunc('quarter', date), brand;

You can do something like this for all the tables you want to create. Note that the time period is an explicit column.

Upvotes: 1

Related Questions