SQL Running Total | Advance SQL | Rolling N months SUM, AVG, MIN, MAX

In this video we will learn how to find running/rolling calculations in SQL. This is very important concept and very frequently used in analytical projects.

[ianpropst-campbell]

Thank you for your helpful explanation of rolling calculations!

[sumksa]

Thank you so much. May you have all the success in your life 🙏🙏

[Aarohi_Zara]

select year(order_date), month(order_date), sum(sales)
, sum(sum(sales)) over(order by year(order_date), month(order_date)
rows between 2 preceding and current row)
, avg(sum(sales)) over(order by year(order_date), month(order_date)
rows between 2 preceding and current row)
, max(sum(sales)) over(order by year(order_date), month(order_date)
rows between 2 preceding and current row)
, min(sum(sales)) over(order by year(order_date), month(order_date)
rows between 2 preceding and current row)
from orders
group by year(order_date), month(order_date)
order by 1, 2

[kanchidoshi]

Good content. Can you please make more business scenario specific videos?

[fozantalat]

Hello Ankit, My name is Fozan I'm from Pakistan, your videos are very helpful please also make assignment questions, so that we can test our understanding.

[abhishekarora]

Does proceeding keyword is specific to Microsoft SQL Server or it would work across different RDBMS?

[mr]

How to calculate the prior rolling 12 months average (current year = 2023, prior = 2022) -- I tried this: rows 24 preceding and 12 preceding

[vandanaK-mhzo]

with cte as (
select
datepart(year, order_date) as sales_year,
datepart(month, order_date) as sales_month,
sum(sales) as sales
From cust_orders
group by sales_year, sales_month)
select *,
sum(sales) over (order by sales_year asc, sales_month asc rows between 2 preceding and current row) as rolling_sum,
min(sales) over (order by sales_year asc, sales_month asc rows between 2 preceding and current row) as rolling_min,
max(sales) over (order by sales_year asc, sales_month asc rows between 2 preceding and current row) as rolling_max,
avg(sales) over (order by sales_year asc, sales_month asc rows between 2 preceding and current row) as rolling_avg
from cte;

[amangupta]

Hi Ankit, Could you please provide data for this tutorial?
I practice side by side as I go by the video. Thanks in advance.

[mithunnambiar]

Hi Ankit, A request... Could you please provide the schema of this query?

[utkarshtrivedi]

Also it will be very helpful if you can provide the data with each video to practice it also.

[sashipanda]

how can we apply the same logic in postgre please help

[harishkanta]

Ankit bhai what does unbounded preceding and current following give.

[shanthankasula]

0 preceding and current row functioning is same

[explorer_baba]

Hi Ankit, how can we find rolling sum without using any windows function . Today, One interviewer has asked me this question. If anyone has any idea can share their thoughts.

[SunilKumar_]

I am working on writing a query which will give the number of hours left on starting day and it should gradually decrease towards end day and reach zero. Can you please create a video for this?

[shreyashchoudhary]

please provide data, create and insert stmt, github or here

[utkarshtrivedi]

Please make a video on NTILE, LAG and LEAD function also