CODEWITHSUNDEEP
sqlsql-serverjoinleft-joinself-join
A.Shoman
A.Shoman

Reputation: 3085

SQL - Calculate the difference in number of orders by month

I am working on the orders table provided by this site, it has its own editor where you can test your SQL statements.

The order table looks like this

order_id customer_id order_date
1 7000 2016/04/18
2 5000 2016/04/18
3 8000 2016/04/19
4 4000 2016/04/20
5 NULL 2016/05/01

I want to get the difference in the number of orders for subsequent months.

To elaborate, the number of orders each month would be like this

SQL Statement

SELECT 
    MONTH(order_date) AS Month,
    COUNT(MONTH(order_date)) AS Total_Orders
FROM
    orders
GROUP BY
    MONTH(order_date)

Result:

Month Total_Orders
4 4
5 1

Now my goal is to get the difference in subsequent months which would be

Month Total_Orders Total_Orders_Diff
4 4 4 - Null = Null
5 1 1 - 4 = -3

My strategy was to self-join following this answer

This was my attempt

SELECT 
    MONTH(a.order_date),
    COUNT(MONTH(a.order_date)),
    COUNT(MONTH(b.order_date)) - COUNT(MONTH(a.order_date)) AS prev,
    MONTH(b.order_date)
FROM 
    orders a
LEFT JOIN 
    orders b ON MONTH(a.order_date) = MONTH(b.order_date) - 1
GROUP BY
    MONTH(a.order_date)

However, the result was just zeros (as shown below) which suggests that I am just subtracting from the same value rather than from the previous month (or subtracting from a null value)

MONTH(a.order_date) COUNT(MONTH(a.order_date)) prev MONTH(b.order_date)
4 4 0 NULL
5 1 0 NULL

Do you have any suggestions as to what I am doing wrong?

Upvotes: 0

Views: 874

Answers (2)

Emin Mesic
Emin Mesic

Reputation: 1811

You have to use LAG window function in your SELECT statement.

LAG provides access to a row at a given physical offset that comes before the current row.

So, this is what you need:

SELECT 
    MONTH(order_date)           as Month,
    COUNT(MONTH(order_date))    as Total_Orders,
    COUNT(MONTH(order_date)) - (LAG (COUNT(MONTH(order_date))) OVER (ORDER BY (SELECT NULL))) AS Total_Orders_Diff
FROM orders
GROUP BY MONTH(order_date);

Here in an example on the SQL Fiddle: http://sqlfiddle.com/#!18/5ed75/1

Solution without using LAG window function:

WITH InitCTE AS
(
    SELECT MONTH(order_date) AS Month,
           COUNT(MONTH(order_date)) AS Total_Orders
    FROM orders
    GROUP BY MONTH(order_date)
)
SELECT InitCTE.Month, InitCTE.Total_Orders, R.Total_Orders_Diff
FROM InitCTE
      OUTER APPLY (SELECT TOP 1 InitCTE.Total_Orders - CompareCTE.Total_Orders AS Total_Orders_Diff
                   FROM InitCTE AS CompareCTE
                   WHERE CompareCTE.Month < InitCTE.Month) R;

Upvotes: 2

Stu
Stu

Reputation: 32614

Something like the following should give you what you want - disclaimer, untested!

select *, Total_Orders - lag(Total_orders,1) over(order by Month) as Total_Orders_Diff
from (
 select Month(order_date) as Month, Count(*) as Total_Orders
 From orders
 Group by Month(order_date)
)o

Upvotes: 1

Related Questions