Architucas Architucas - 2 years ago 60
SQL Question

Syntax to get sum(sales) group by brand but different date

My data is like so

item date country sales
----------------------------------------
motorola 2015-01-01 US 10
motorola 2015-01-01 UK 20
motorola 2015-01-02 US 40
motorola 2015-01-02 UK 80
motorola 2015-01-03 US 120
motorola 2015-01-03 UK 150
motorola 2015-01-04 US 170
motorola 2015-01-04 US 180


I want to get the daily sales delta of motorola from 2 jan 2015 until 4 jan 2015.

So for example


  • total sales for 1 jan 2015 is 10 (US) + 20(UK) = 30

  • total sales for 2 jan 2015 is 120 so daily sales delta (sales on date minus D-1) is 90

  • total sales for 3 jan 2015 is 270 so daily delta is 150

  • total sales for 4 jan 2015 is 350 so daily delta is 80



I'm expecting the result tuple :

date dailyDelta
2015-01-02 90
2015-01-03 150
2015-01-04 80


What is the syntax to get this? I'm using SQL Server 2012.

Thanks

Answer Source

This is it, the query logic is as simple as it gets, the performance better than inner joins:

select date, sum(sales) - coalesce(lag(sum(sales), 1) over (order by date), 0)
from my_sales
group by date
order by date

Use window function lag. Play with it: http://sqlfiddle.com/#!6/bebab/8 and read about it: https://msdn.microsoft.com/en-us/library/hh231256.aspx

briefly, lag(sum(sales), 1) over (order by date) means "get sum(sales) column of previous record of this query, ordered by date", coalesce(XXX, 0) means "when XXX is null, let's pretend it was a zero"

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download