Problem with Running SUM in SQL | Watch it to Avoid The Mistake

There is a specific issue with running calculation in SQL when we have duplicated on order by columns. I will discuss 2 solutions in this video on how you can fix the issue.

Most Asked Join Based Interview Question:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

#sql #dataengineer #runningcalc

[vandanasharma]

unbounded preceding -> means window starting the first row of the resultset
current row -> means current row for which you are calculating value
unbounded Following -> means window ending the last row of the resultset

select *,
sum(cost) over (order by cost asc) --duplicates causing issue
from products;

--method 1
select *,
sum(cost) over (order by cost asc, product_id ) --use another key column for unique records during sort and summation
from products;

--method 2
select *,
sum(cost) over (order by cost asc rows between unbounded preceding and current row)
from products;

[lamborghiniveneno]

3 minutes 42 seconds... Totally worth it!!!! Thanks Ankit!! Always a gem!!

[ShivaKumarTeam]

Table for above example

create table products ( product_id varchar(20), cost int)

insert into products values ('P1', 200), ('P2', 300), ('P3', 300), ('P4', 500), ('P5', 800)

[mokshjaiswal]

Your video was short as well as easy to understand. Very helpful video.

[parveen]

Running cost without window function. btw Window fn are more simple

select p1.product_id, sum(p2.cost)
from products p1 left join products p2 on p1.product_id>= p2.product_id
group by p1.product_id

[sharu]

Thank you Ankit for explaining in such simple terminologies, it makes it so much more interesting & relatable 😇

[akshobshekhar]

Hi Ankit we can simply use "rows between unbounded preceding and current row" without order by that also does the trick

[adityabaha]

Hi Ankit, Thanks for this beautiful piece of tip. Could you please create a video to explain the concept of "rows between unbounded preceding and current row" with some more options please?? Thanks in Advance 😀

[sumibits]

Hello Ankit, Quick question... do you take SQL online class ? If Yes, I am interested. If No, Can I grab your 30 mins on calendar ? I am looking for some guidance from you. Thank you

[taniyasaini]

Awesome! Waiting for the next video :)

[SACHINKUMAR-pxkq]

Thankyou so much sir, I watched your All Videos on SQL sir, Thankyou so much sir 😍 Feel Like SQL PRo

[SouravKumar-vnpp]

with cte as (
select *, rank() over (order by product_id) as rnk from prod
)
select cte.*, sum(cost) over(order by rnk asc) as running_cost from cte

[abhishek_grd]

Great Concept.
Can you please make video on lead, lag functions use cases as well and more on unbounded precedence?

[chirodiplodhchoudhury]

Hey Ankit, can you make a video about how to calculate running difference? Also concepts like Rows and Range, unbounded preceding following etc on window functions?

[percyjackson]

Hi Ankit, Thanks for posting such helpful videos 💯💯.
On difference b/w just order by and order by rows, mysql doesnt create any issues. Im getting same result [running_sum_1, running_sum_2]. Am i missing out anything here?

with products as (
select * from (
select 'p1' as prd_id, 200 as cost
union
select 'p2' as prd_id, 300 as cost
union
select 'p3' as prd_id, 300 as cost
union
select 'p4' as prd_id, 500 as cost
union
select 'p5' as prd_id, 800 as cost
) as x
)

select *
, sum(cost) over (order by prd_id) as running_sum_1
, sum(cost) over (order by prd_id rows BETWEEN UNBOUNDED PRECEDING and 0 PRECEDING) as running_sum_2
from products

[vansh]

what if cost is not in ascending order?

[Paras]

Hello Ankit, Can't we use distinct function just to get the unique values along with the running sum function, in the problem?

[madhusudhangoud]

Hi Ankit, the second logic which you explained will work only in mysql ? or will it work on hive?

[vijayvishwakarma]

Please bro make a video purchase and sale unit fifo method how to implement in SQL

[adityabaha]

Hi @Ankit, I've come across interview questions around Running Avg., Could you please explain its relevance & how we can achieve it? Also, can there be other rolling aggregations? Could you pls make a video on this topic if you think this will be helpful to all?

Thanks for everything you do for us!! 🙂