CODEWITHSUNDEEP
sqldate
user2888246
user2888246

Reputation: 185

SQL partition by dates, sum by dates

I'm using Teradata I have a table which shows me sales numbers by each day. I want to do something like

select product
    , sum(sales) over (partition by product) where dates between date '2013-10-01' and date '2013-12-12' as Q4Sales
    , sum(sales) over (partition by product) where dates between date '2013-07-01' and date '2013-10-31' as Q3Sales
    from tablex
    qualify 
      row_number()
         over(partition by dates, product)

I can't seem to find how I do this summing with where clause. Can someone help. I've searched the internet but can't find what I'm looking for.

Upvotes: 1

Views: 4912

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Try using conditional aggregation:

select product,
       sum(case when dates between date '2013-10-01' and date '2013-12-12' then sales end) over
           (partition by product
           ) as Q4Sales
       sum(case when dates between date '2013-07-01' and date '2013-10-31' then sales end) over
           (partition by product
           ) as Q3Sales
from tablex
qualify row_number() over(partition by dates, product)

Upvotes: 2

Related Questions