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-campbell6028]

Thank you for your helpful explanation of rolling calculations!

[@mgopinath9080]

Amazing😍

[@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

[@DeshaKannada]

Great 🎉

[@sumksa]

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

[@ankitbansal6]

Thank you 🙏

[@sachinkumargupta5660]

Awsome

[@TienDuong-ck4jf]

Thanks for your clear instruction :D

[@ankitbansal6]

You're welcome!

[@mr2262]

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

[@ankurrunthala]

Very good explanation 🙂

[@ankitbansal6]

Thank you 🙂

[@kanchidoshi6907]

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

[@ankitbansal6]

sure

[@sudarshanthota4444]

Nice video 👍

[@ankitbansal6]

Thank you 😊

[@fozantalat4509]

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.

[@ankitbansal6]

Hi Fozan, Good to know my Pakistan friends are getting some benefit out of my videos. I will do that 🙂

[@shobhitsharma2137]

how to get dataset

[@yatinshekhar787]

9/142

[@mithunnambiar1433]

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

[@ankitbansal6]

You can download superstore data from this link
drive.google.com/drive/mobile/folders/1Dc81McsB4lp1JUIwssDmmOaw6Z7rBK8T?usp=sharing

[@explorer_baba2750]

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.

[@ankitbansal6]

Can be done using self join

[@abhishekarora1]

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

[@ankitbansal6]

It works across most of the databases

[@abhishekarora1]

@@ankitbansal6 thanks for quick reply!

[@harishkanta3711]

Ankit bhai what does unbounded preceding and current following give.

[@ankitbansal6]

Running sum

[@harishkanta3711]

@@ankitbansal6 thank you

[@sashipanda3405]

how can we apply the same logic in postgre please help

[@sashipanda3405]

adding to above if there are year,month,weeks column

[@utkarshtrivedi9949]

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

[@ankitbansal6]

Okay

[@amangupta1959]

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

[@ankitbansal6]

You can download data from below link:
drive.google.com/drive/folders/1Dc81McsB4lp1JUIwssDmmOaw6Z7rBK8T?usp=sharing
In below video I have explained how to import the data:
ruclips.net/video/WIsPyYvEV2c/видео.html

[@amangupta1959]

@@ankitbansal6 thank you

[@SunilKumar_67]

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?

[@ankitbansal6]

24- hour(sysdate)

[@vandanaK-mh9zo]

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;

[@shreyashchoudhary6827]

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

[@ankitbansal6]

Provided in today's video

[@utkarshtrivedi9949]

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

[@ankitbansal6]

Sure

[@vishalsonawane.8905]

Please add the data

[@sky4223]

Sir Hume chotte bacche ko dekhna hai

[@ankitbansal6]

Haha 😃

[@sky4223]

Kab dikhaoge