ZJAY ZJAY - 1 month ago 4
SQL Question

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

Answer

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.

Comments