SQL - Calculate the difference in number of orders by month

Question

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?

@Stu there's an assumption (related to my task) that window functions are not available. This basically means that there's a solution that does not depend on lag — Abdelrahman Shoman, Apr 02 '21 at 19:25
Ah - ok well I just posted an example that uses `lag` - sorry, you didn't provide a list of syntax not to be used lol ;-) — Stu, Apr 02 '21 at 19:28
Does this answer your question? [SQL - get summary of differences vs previous month](/q/63737197/90527) — outis, Oct 24 '22 at 22:47

Emin Mesic · Accepted Answer · 2021-04-02T19:54:26.620

2

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;

edited Apr 02 '21 at 19:54

answered Apr 02 '21 at 19:26

Emin Mesic

1,681
2
8
18

there's an assumption (related to my task) that window functions are not available. This basically means that there's a solution that does not depend on lag. Also your statement results in a parsing error "SyntaxError: Parse error on line 4: ...MONTH(order_date))) OVER (ORDER BY (SELE" – Abdelrahman Shoman Apr 02 '21 at 19:31
1

@AbdelrahmanShoman this solution will work if you execute directly on the database, it will not work if you try on the attached website because it doesn't support window functions. I have updated my answer and attached SQL Fiddle link. – Emin Mesic Apr 02 '21 at 19:34
Maeisc, I see, thanks. However, I am working under the assumption I can't use window functions (this related to my task). That's why I am assuming the solution would be using joins – Abdelrahman Shoman Apr 02 '21 at 19:38
1

@AbdelrahmanShoman I have written a statement for you without using window functions. – Emin Mesic Apr 02 '21 at 19:54

score 1 · Answer 2 · answered Apr 02 '21 at 19:27

1

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

answered Apr 02 '21 at 19:27

Stu

30,392
6
14
33

this will get me a syntax error "SyntaxError: Parse error on line 1: ...lag(Total_orders,1) over(order by Month)". You can try it here: https://www.techonthenet.com/sql/select_try_sql.php – Abdelrahman Shoman Apr 02 '21 at 19:35
@AbdelrahmanShoman the same query will return correct results if you try on the SQL Fiddle link I attached in my answer. – Emin Mesic Apr 02 '21 at 19:37
1

@AbdelrahmanShoman It looks like that does not support SQLServer syntax in that scenario - the `lag` statement above is fine, I've just checked it in SQLServer. – Stu Apr 02 '21 at 19:40

SQL - Calculate the difference in number of orders by month

2 Answers2