CODEWITHSUNDEEP
sqlgroup-byaggregate-functions
lilloraffa
lilloraffa

Reputation: 1397

SQL: aggregation (group by like) in a column

I have a select that group by customers spending of the past two months by customer id and date. What I need to do is to associate for each row the total amount spent by that customer in the whole first week of the two month time period (of course it would be a repetition for each row of one customer, but for some reason that's ok ). do you know how to do that without using a sub query as a column? I was thinking using some combination of OVER PARTITION, but could not figure out how... Thanks a lot in advance. Raffaele

Query:

select customer_id, date, sum(sales)
from transaction_table
group by customer_id, date

Upvotes: 0

Views: 89

Answers (1)

APH
APH

Reputation: 4154

If it's a specific first week (e.g. you always want the first week of the year, and your data set normally includes January and February spending), you could use sum(case...):

select distinct customer_id, date, sum(sales) over (partition by customer_ID, date)
, sum(case when date between '1/1/15' and '1/7/15' then Sales end) 
    over (partition by customer_id) as FirstWeekSales
from transaction_table

In response to the comments below; I'm not sure if this is what you're looking for, since it involves a subquery, but here's my best shot:

select distinct a.customer_id, date
, sum(sales) over (partition by a.customer_ID, date)
, sum(case when date between mindate and dateadd(DD, 7, mindate) 
    then Sales end) 
    over (partition by a.customer_id) as FirstWeekSales
from transaction_table a
left join 
(select customer_ID, min(date) as mindate 
    from transaction_table group by customer_ID) b
on a.customer_ID = b.customer_ID

Upvotes: 1

Related Questions