All About SQL Aggregations | SQL Advance | Zero to Hero

In this video we will discuss all flavors of aggregation in SQL. There are many advance concepts wrt aggregations and we will cover everything possible. Do watch this video till end to master SQL aggregation.
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Tricky SQL Problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
DDL and DML:
CREATE TABLE [dbo].[int_orders](
[order_number] [int] NOT NULL,
[order_date] [date] NOT NULL,
[cust_id] [int] NOT NULL,
[salesperson_id] [int] NOT NULL,
[amount] [float] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[int_orders] ([order_number], [order_date], [cust_id], [salesperson_id], [amount]) VALUES (30, CAST(N'1995-07-14' AS Date), 9, 1, 460)
INSERT [dbo].[int_orders] ([order_number], [order_date], [cust_id], [salesperson_id], [amount]) VALUES (10, CAST(N'1996-08-02' AS Date), 4, 2, 540)
INSERT [dbo].[int_orders] ([order_number], [order_date], [cust_id], [salesperson_id], [amount]) VALUES (40, CAST(N'1998-01-29' AS Date), 7, 2, 2400)
INSERT [dbo].[int_orders] ([order_number], [order_date], [cust_id], [salesperson_id], [amount]) VALUES (50, CAST(N'1998-02-03' AS Date), 6, 7, 600)
INSERT [dbo].[int_orders] ([order_number], [order_date], [cust_id], [salesperson_id], [amount]) VALUES (60, CAST(N'1998-03-02' AS Date), 6, 7, 720)
INSERT [dbo].[int_orders] ([order_number], [order_date], [cust_id], [salesperson_id], [amount]) VALUES (70, CAST(N'1998-05-06' AS Date), 9, 7, 150)
INSERT [dbo].[int_orders] ([order_number], [order_date], [cust_id], [salesperson_id], [amount]) VALUES (20, CAST(N'1999-01-30' AS Date), 4, 8, 1800)
#sql #dataengineer #aggregations

[@ankitbansal6]

If you want to master SQL check out my zero to hero course here : www.namastesql.com/courses/SQL-For-Analytics-6301f405e4b0238f71788354

[@itsme_notbatman]

Hello Sir, the mentioned site is not reachable.. is it down?

[@ankitbansal6]

@@itsme_notbatman it's working. Try again

[@tapashidutta5136]

Your videos helped me crack Data Engineering interview in one of the Big 4 companies. Thank You!!

[@ankitbansal6]

Awesome. Congratulations 🎉

[@VikramKumar-dq2is]

R u fresher

[@tapashidutta5136]

@@VikramKumar-dq2is, having a total of 2.5 yrs experience now.

[@bilal5360]

Distribute sweets

[@rihanqureshi1265]

@tapashidutta5136 Please help me to join the Data Engineering journey

[@ishatiwari3023]

Awesome video! Cleared all my doubts around aggregation functions in SQL.

[@kuldeepbisht9308]

Hello sir, I love your content. Instead of roaming here and there for SQL knowledge you have become one stop solution. Please keep on posting

[@ankitbansal6]

Thank you 😊

[@shubhammeshram8504]

Great and Excellent teaching. Thank you for sharing. I have learned a lot.

[@AnandKumar-dc2bf]

Excellent content bro , Others who teach dont even cover so much depth topics.. keep gng bro...

[@vivekkumarsingh9009]

Great video. I remember a few years backs when I was struggling to understand what the partition by and order by clauses do together and separately. This is a great refresher.

[@ankitbansal6]

😊

[@susmitadolas5178]

Hi Ankit, I have started watching your videos recently and I have started growing much more interest and curiosity and understanding and learning sql. Thank you so much! My sql knowledge gets increased with each and every video of yours. Do keep up the great work and keep us inspiring and educating. :)

[@ankitbansal6]

Thank you

[@shafi123]

first complete video in the KZbin which covers all these aggregation.. Thank You

[@ankitbansal6]

Thank you 😊

[@janakmali8243]

Very Good explanation on each level. Excellent Contents.

[@meghnasoni]

Hands down the best channel for analytics and SQL on youtube.

[@ankitbansal6]

Thank you 😊

[@avsmlrit]

I had gone through many videos...finally arrived to a FINEST and THE BEST..Thank you for your efforts..Please keep sharing knowledge.

[@ankitbansal6]

Thank you 😊

[@florincopaci6821]

You are the best! every day i learn from you! yesterday i read your post on LinkedIn about that trick when you divide 2 integers today i learn how to make lead and lad with sum .THANK YOU!!!

[@ankitbansal6]

I am delighted ☺️

[@arpiteshsrivastava9838]

Maza agya, Thank you Ankit. Knowing how to work with lead and lag fn w/o actually using them, instead using preceding/following is awesome.

[@ankitbansal6]

My pleasure

[@shailjamishra9423]

Very Helpful. Thank you very much!

[@pratiksingh9480]

Just awesome , as it says Zero to Hero , beautifully covered different aspects of a confusing topic elegantly

[@ankitbansal6]

Glad to hear that

[@elvirosukuzu6519]

Thanks for your videos. I have learned a lot. I believe it is not easy to make videos. Thanks for your time.

[@NamanSeth1]

You doing great work Ankit. Keep posting and Keep educating. It is really helping many of us. Thanks!

[@ankitbansal6]

Thank you 😊

[@gauravarora5165]

Came across this video today and thought of checking to refresh concepts and i would say you explained every aspect and usecase of aggregation functions very well !

[@ankitbansal6]

Awesome, thank you!

[@divyakrish8130]

Much appreciated. You are really inspiring to learn SQL interestingly. God bless you and all the best.

[@ankitbansal6]

Happy to hear that!

[@ravitejatavva7396]

Loved it! you nailed it. Keep growing and keep sharing! 😍

[@ankitbansal6]

Thank you so much 😄

[@Hkumar_new]

Very well... Really learning....

[@nidhisingh4973]

Amazing. Thank you so much

[@PradyutJoshi]

THIS right here is the masterpiece! I have read n number of articles of windows functions and still got confused but you so simply and beautifully explained the whole thing in under 18 minutes. Hats off to you, sir 🙌🙌🙌

[@ankitbansal6]

Thanks Pradyut. It means a lot 😊

[@saisumanthnagasuri7741]

Hi @@ankitbansal6 Sir, I have inserted two records ,matching the order_date with two existing records. INSERT [dbo].[int_orders] ([order_number], [order_date], [cust_id], [salesperson_id], [amount]) VALUES (80, CAST(N'1995-07-14' AS Date), 9, 3, 500); INSERT [dbo].[int_orders] ([order_number], [order_date], [cust_id], [salesperson_id], [amount]) VALUES (90, CAST(N'1998-02-03' AS Date), 2, 7, 1000); select salesperson_id ,order_number,order_date,amount, sum(amount) over (order by order_date) from int_orders order by order_date,salesperson_id ; I didnt use partition by clause in the above query,but why the first Amount value is 960 not 460. can you please explain how this works in the backend? SALESPERSON_ID ORDER_NUMBER ORDER_DATE AMOUNT TOTAL_SALES 1 30 1995-07-14 460 960 3 80 1995-07-14 500 960 2 10 1996-08-02 540 1,500 2 40 1998-01-29 2,400 3,900 7 50 1998-02-03 600 5,500 7 90 1998-02-03 1,000 5,500 7 60 1998-03-02 720 6,220 7 70 1998-05-06 150 6,370 8 20 1999-01-30 1,800 8,170

[@mounikabairi4530]

It is added based upon order_date and value added you ate expecting first value need to be 460 then u need use partition by salespersonid

[@venkateshgolla8005]

Excellent, you made SQL easy to learn. You are really good teacher. Keep it up 👍

[@ankitbansal6]

Thank you 😊

[@killianjude9477]

Amazing work Ankit Bhai, keep making these suprb videos

[@ankitbansal6]

Thank you 😊

[@abhigyandatta2008]

masterclass to clear aggregation concepts once and for all. Thanks Ankit.

[@ankitbansal6]

Keep watching🙏

[@suumit2008]

Just a masterclass, the way you teach is simply outstanding ....Jem of a person :)

[@ankitbansal6]

Glad you enjoyed it😊

[@aryabhatt5833]

One of the best few videos on Window Functions! Or I must say 'The Best'...

[@ankitbansal6]

Glad it was helpful!

[@pratyushkumar361]

I am loving your videos now. Please create a video detailing about all the types of indexes. Really appreciate your efforts. Thanks

[@ankitbansal6]

Sure will do 🙂

[@rk-ej9ep]

This is awesome.. 😎

[@maheshbhadane1520]

Thank you Sirji...What a masterpiece from your side for SQL....I request you that please make more videos on other imp topic also like Stored procedure , CTE, View, Indexes, etc....I'm desperately waiting to learn these topics from you....Thank you so much again GBU

[@ankitbansal6]

Thank you. Sure :)

[@user-js2vt3nz2k]

Hi Ankit, Thanks for posting this video I went through this and understood whole concept. I really appreciate you for explaining in simple way. Thanks Again!

[@ankitbansal6]

You're most welcome

[@abdulwahab-rw1yq]

Thanks much Ankit..i have my interview today.. and your videos are one point stop for me .. thanks again

[@ankitbansal6]

Best wishes 🙂

[@shaikmahaboob4766]

Great explanation 👍

[@rushikeshwaghmare3446]

Awesome video❤

[@VehicleEnthusiastic3575]

Hello Ankit, Really mind blowing explanation along with simple content. Very helpful.👌👌

[@ankitbansal6]

Thank you 😊

[@sumibits]

Truly insightful. Thanks for sharing Ankit.

[@ankitbansal6]

Thanks Sumeet 🙏

[@omkarm7865]

Fresh morning and new learning...awesome video

[@ankitbansal6]

Thank you 😊

[@sambitguru8374]

It was really very helpful. You have clearly explained the concept of using aggregate function with window function and rows between _ preceding and _ following with the help of examples. Earlier, I had a lot of doubts in these concepts but now after watching your video, all my doubts are cleared. Thanks a lot!!

[@ankitbansal6]

Thank you 😊

[@sriharshakallakuri4046]

Thank you sir for sharing such insights getting to know all the things related why and how it is 👍👏

[@ankitbansal6]

🙏

[@shorakhutte1887]

Very well explained. Thanks for sharing amazing content as you always do.

[@ankitbansal6]

My pleasure!

[@ravishmahajan9314]

Awesome👏

[@sidindian1982]

Excellent Explanation... Wat a Class Sir ... Brilliant ...😍😍🤩🤩🤩

[@ankitbansal6]

Keep watching💪

[@adityabaha]

I had requested this Video, Thank you so much Ankit 🙂….🙏🏻

[@ankitbansal6]

Thanks Aditya. It was much needed 🙂

[@surajchavan6447]

Awesome 👍🏻

[@kanchankumar3355]

Very informative , explanation is easy to understand!!

[@ankitbansal6]

Thanks bro 😊

[@vinaykumarreddykonde4065]

Your way of approach and explanation was excellent.

[@ankitbansal6]

Glad it was helpful!

[@PankajKumar-nb1jg]

Great....very informational video...thanks sir 👍

[@ankitbansal6]

Good to know 😊

[@Mysingh9767]

Thanks Ankit....

[@shaikmahaboob4766]

My all doubts solved by this video

[@sagarverma2708]

Thankyou so much sir for providing these videos on practical sql, please make videos on basics to advanced of CTE/Stores Procedures/Views also.

[@ankitbansal6]

Sure

[@prahlad8]

Thank you Ankit your explanation is so simple

[@ankitbansal6]

Thank you 😊

[@kartikpidurkar9590]

Hi Ankit, I am unable to find the video in which you explained the difference between unbounded preceding and simple order by for rolling calaculations.Can you please attach the link?

[@akshatjaggi253]

Hi Ankit, just wanted to tell you that your videos are really helpful. You explain everything clearly without leaving anything out, and your content is informative as well as engaging. So glad, I came across your channel.

[@ankitbansal6]

Good to hear that ☺️

[@user-pe9kp1vh2e]

i just love the way you teach

[@ankitbansal6]

Thank you 😊

[@innominatesoloist1597]

my god! awesome content

[@ankitbansal6]

Thank you 😊

[@shubhampalaskar8374]

This is Amazing 👏

[@ankitbansal6]

Thanks Shubham 😊

[@manojkumar-kq1nc]

Awesome

[@SM-gz7vw]

Hi sir, what is the difference between unbounded preceding and simple order by for running totals? I couldn't find the answer in the video. Pls answer this

[@rajatjain3082]

Please add video on partition by multiple columns

[@ganeshkumar1424]

As usual you nailed it .. can you create similar video for windows function zero to Hero …

[@ankitbansal6]

Sure

[@swamivivekananda-cyclonicm8781]

Fantastic.

[@ankitbansal6]

Thank you! Cheers!

[@VirD2023]

i asked my trainer but this solved my problem!! Thanks sir

[@ankitbansal6]

Most welcome

[@shuaibsaqib5085]

Nice way of teaching🙂

[@ankitbansal6]

Keep watching

[@rishabhgupta7726]

Thanks so much Ankit. Can we have next video on joins? . It is much needed and would help in demystifying this topic.

[@ankitbansal6]

I have a video kzbin.info/www/bejne/nIebeox-hNGChMk

[@CanadaTorontoLife]

Hello Sir.. Very informative video, thanks a lot. I shared your channel with my colleagues so that they can benefit from it. Could you please plan to share a video explaining learning path to follow to learn python for data engineers.

[@ankitbansal6]

Thank you so much. Sure will create the road map.

[@prasadchowdary4870]

When creating the tables Why the columns and insert datais kept in the braces

[@Jerry-tz2uk]

Nice video, Could you please also make tutorials for - "Creating a list of dates/months/weeks between start date and end date (I'm trying to display active records over time using start and end date - Data scaffolding method)?

[@zub3rahmed76]

@ankitbansal6 yes , please make video for above query

[@shridharshitoot8582]

Hey Ankit, I am getting syntax error on 'order'. pls help. @7:59 select salesperson_id,order_number,order_date,amount ,sum(amount) over(partition by salesperson_id order by order_date) from int_orders; Incorrect syntax near 'order'.

[@suneelsunkari4354]

Hello Sir, If you can add sum(case when .. then .. else .. end) over (partition by .. order by .. rows between .. ) as .. then users will get end to end idea sir.This is from your previous video sir.Kindly append so that new users can get best out of this video.It's already best btw 😀.Just letting you know you can add more cheese.I am one of the first 100 subscribers of this channel.Ever since then i never looked into any other youtube channel or any other udemy course.If we know basic sql and looking for scenario based questions this channel is one stop solution place.Hope you like my comment sir.

[@ankitbansal6]

It's there in the video 🙂

[@soumyapadhee]

excellent

[@ankitbansal6]

Thank you 😊

[@balajir724]

Sir. Great Video. You said that you will tell the differences between normal running sum and unbounded preceding and current row.

[@ankitbansal6]

Check this kzbin.info/www/bejne/lXjOgZRtft5lfM0

[@balajir724]

@@ankitbansal6 Thank You Sir

[@satyamgour9461]

Why it gives running sum only when we use order by?

[@dataquery03382]

After watching your videos, SQL looks so much easier.

[@ankitbansal6]

Glad to hear that!

[@abhishek_grd]

Amazing video. Ankit can you put all videos in your created playlist. It is covienent to track. This video I am not seeing in any playlist

[@ankitbansal6]

I think it should be there in tips and tricks. I will check anyways.

[@anujgupta-lc1md]

Can we seen something realted to performance tunings/ indexes in Tsql

[@ankitbansal6]

Sure.

[@jaynee96]

Hi sir can you create videos from scratch for beginners

[@nishigandhapanchal5317]

Hi Ankit, I have one question- If i have a table like below Id Name Sal 1 A. 1000 2 B. 1500 3 C. 2000 4 D. 2000 I want to fetch the max salary, but i want all the records with max salary and i need to fetch all the column data to be displayed.

[@anyuser9]

Hello Ankit I have one question. As you said we can use 1 preceding and 1 preceding function which works similar to Lag function. But let's say when there is too much data in the database and if we use the " 1 preceding and 1 preceding " function then how the system will behave in terms of performance. Can we use " 1 preceding and 1 preceding " for query optimisation???

[@ankitbansal6]

Lead/lag is meant for that then use that. It is just a work around.

[@anyuser9]

@@ankitbansal6 Got It. Thank you 😊 🙏

[@rahulyeole6411]

Is there any all video playly video of sql

[@ankitbansal6]

Complex SQL Questions for Interview Preparation: kzbin.info/aero/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb

[@raushankumar-ry7so]

which database you have used in this tutorial Ankit ji

[@ankitbansal6]

SQL server

[@HarshKukreja052]

Hello Sir, Can you explain how unbounded preceding and current row is different from just order by order_date. Query would give the same result I believe

[@ankitbansal6]

Watch this kzbin.info/www/bejne/lXjOgZRtft5lfM0

[@HarshKukreja052]

Understood.. thank you sir 🙏

[@shubhammajhi7493]

Hi sir and everyone, I am stuck with a query: suppose we have a transactions table with (timestamp, user_id, sender_id, amount) fields how to pick those transactions that happen within an hour by a particular user (suppose a user starts 1st trans at 8am, 2nd at 8.25am, 3rd at 9.05am, 4th at 9.50am, 5th at 10.45am... so all of these records must be picked starting from 8am to 10.45am) can anyone please suggest an approach to solve this... 🙏

[@ankitbansal6]

What do you mean by within an hour. You are saying all the transaction should be picked up but they are accross multiple hours

[@shubhammajhi7493]

@@ankitbansal6 we have to pick series of transactions uptil time between two consecutive transactions is less than one hour Eg - for a user he makes following transactions in a day 1. 8 am 2. 8.25 am 3. 9.10 am 4. 9.45 am 5. 12.35 pm 6. 3.20 pm 7. 4.45 pm 8. 5.15 pm 9. 5.50 pm 10. 10pm Out of above transactions we should be able to pick trans no 1,2,3,4 since in this series the time diff between consecutive trans is less than an hour, likewise we should also be able to pick trans no 7,8,9 here also diff between consecutive trans is less than an hour... Ps - if you could provide me anyway(discord server, mail, etc) to share the sample dataset and full query requirement for much better understanding ??

[@ankitbansal6]

@@shubhammajhi7493 you can send me an email [e-mail]

[@shubhammajhi7493]

@@ankitbansal6 okay thanks 🙂

[@SagarKumar-hh8kt]

It would have been nice if you had explained with examples.

[@jacobreddydevidi1305]

hi ankit 17/32 video completed

[@ankitbansal6]

Keep watching

[@jacobreddydevidi1305]

namasthey python course student, thanks for the SQL content, I appreciate your efforts didn't complete Python (start as well).

[@randomstuff8813]

CFBR

[@ankitbansal6]

🙏

[@ankushkale616]

Share sql playlist please

[@ankitbansal6]

Complex SQL Questions for Interview Preparation: kzbin.info/aero/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb

[@tazulislam2698]

Brother I have a problem in sql. Sum function give threefold or fourfold result with join query. could you please spend time fom me? I have shown the problem many yutube expert but they are procrastinating only. Thanks

[@ankitbansal6]

Send me problem on [e-mail]

[@tazulislam2698]

Yes Brother. I am sharing the query.

[@tazulislam2698]

@@ankitbansal6 Brother I have sent a demo data and query to mail. Please give me solution on it.

[@ankitbansal6]

@@tazulislam2698 I have not got

[@tazulislam2698]

@@ankitbansal6 Please check mail again. I have resent.

[@soumyagobbani5336]

Great content and clear explanation of aggregate/window functions. With using over (order by order_date), result set will be sorted whereas using over (order by order_date with rows between unbounded preceding and current row) result set will not be sorted in any order. Is that right ?

[@amalkumar5201]

Sir Hindi me vedio banaye please and each step explain why you are doing in hindi

[@bibhutibaibhavbora8770]

kuch samjh nehi aya bhai please try to explain why instead of saying the same thing in english

[@rahulsuwasiya9981]

SQL script: CREATE TABLE int_orders( order_number int NOT NULL, order_date date NOT NULL, cust_id int NOT NULL, salesperson_id int NOT NULL, amount float NOT NULL ) INSERT INTO int_orders VALUES (30, CAST(N'1995-07-14' AS Date), 9, 1, 460) INSERT INTO int_orders VALUES (10, CAST(N'1996-08-02' AS Date), 4, 2, 540); INSERT INTO int_orders VALUES (40, CAST(N'1998-01-29' AS Date), 7, 2, 2400); INSERT INTO int_orders VALUES (50, CAST(N'1998-02-03' AS Date), 6, 7, 600); INSERT INTO int_orders VALUES (60, CAST(N'1998-03-02' AS Date), 6, 7, 720); INSERT INTO int_orders VALUES (70, CAST(N'1998-05-06' AS Date), 9, 7, 150); INSERT INTO int_orders VALUES (20, CAST(N'1999-01-30' AS Date), 4, 8, 1800);

[@kinzorize]

For those of you writing your sql query on Postgresql. You can create the same table here in this Postgresql-compatible format: CREATE TABLE int_orders ( order_number integer NOT NULL, order_date date NOT NULL, cust_id integer NOT NULL, salesperson_id integer NOT NULL, amount float NOT NULL ); INSERT INTO int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (30, DATE '1995-07-14', 9, 1, 460); INSERT INTO int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (10, DATE '1996-08-02', 4, 2, 540); INSERT INTO int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (40, DATE '1998-01-29', 7, 2, 2400); INSERT INTO int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (50, DATE '1998-02-03', 6, 7, 600); INSERT INTO int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (60, DATE '1998-03-02', 6, 7, 720); INSERT INTO int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (70, DATE '1998-05-06', 9, 7, 150); INSERT INTO int_orders (order_number, order_date, cust_id, salesperson_id, amount) VALUES (20, DATE '1999-01-30', 4, 8, 1800);

[@ankitbansal6]

Thank you 😊