All About SQL Aggregations | SQL Advance | Zero to Hero
Вставка
- Опубліковано 15 тра 2022
- 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 - Наука та технологія
If you want to master SQL check out my zero to hero course here :
www.namastesql.com/courses/SQL-For-Analytics-6301f405e4b0238f71788354
Hello Sir, the mentioned site is not reachable.. is it down?
@@itsme_notbatman it's working. Try again
Your videos helped me crack Data Engineering interview in one of the Big 4 companies. Thank You!!
Awesome. Congratulations 🎉
R u fresher
@@VikramKumar-dq2is, having a total of 2.5 yrs experience now.
Distribute sweets
@tapashidutta5136
Please help me to join the Data Engineering journey
Awesome video! Cleared all my doubts around aggregation functions in SQL.
Great and Excellent teaching. Thank you for sharing. I have learned a lot.
Very Helpful. Thank you very much!
Excellent content bro ,
Others who teach dont even cover so much depth topics..
keep gng bro...
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.
😊
Amazing. Thank you so much
Very Good explanation on each level. Excellent Contents.
Hands down the best channel for analytics and SQL on youtube.
Thank you 😊
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. :)
Thank you
Very well...
Really learning....
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
Thank you 😊
first complete video in the UA-cam which covers all these aggregation.. Thank You
Thank you 😊
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 🙌🙌🙌
Thanks Pradyut. It means a lot 😊
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
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
Much appreciated. You are really inspiring to learn SQL interestingly. God bless you and all the best.
Happy to hear that!
Thanks for your videos. I have learned a lot. I believe it is not easy to make videos. Thanks for your time.
This is awesome.. 😎
One of the best few videos on Window Functions!
Or I must say 'The Best'...
Glad it was helpful!
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!!!
I am delighted ☺️
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.
My pleasure
I had gone through many videos...finally arrived to a FINEST and THE BEST..Thank you for your efforts..Please keep sharing knowledge.
Thank you 😊
Great explanation 👍
You doing great work Ankit. Keep posting and Keep educating. It is really helping many of us. Thanks!
Thank you 😊
Just awesome , as it says Zero to Hero , beautifully covered different aspects of a confusing topic elegantly
Glad to hear that
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 !
Awesome, thank you!
Awesome video❤
Awesome👏
Loved it! you nailed it. Keep growing and keep sharing! 😍
Thank you so much 😄
Awesome 👍🏻
Thanks Ankit....
Amazing work Ankit Bhai, keep making these suprb videos
Thank you 😊
masterclass to clear aggregation concepts once and for all. Thanks Ankit.
Keep watching🙏
Fresh morning and new learning...awesome video
Thank you 😊
Great....very informational video...thanks sir 👍
Good to know 😊
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
Thank you. Sure :)
Excellent, you made SQL easy to learn.
You are really good teacher. Keep it up 👍
Thank you 😊
I am loving your videos now. Please create a video detailing about all the types of indexes. Really appreciate your efforts. Thanks
Sure will do 🙂
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!
You're most welcome
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?
Thank you sir for sharing such insights getting to know all the things related why and how it is
👍👏
🙏
Just a masterclass, the way you teach is simply outstanding ....Jem of a person :)
Glad you enjoyed it😊
Thanks much Ankit..i have my interview today.. and your videos are one point stop for me .. thanks again
Best wishes 🙂
Very well explained. Thanks for sharing amazing content as you always do.
My pleasure!
Truly insightful. Thanks for sharing Ankit.
Thanks Sumeet 🙏
My all doubts solved by this video
Awesome
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!!
Thank you 😊
I had requested this Video, Thank you so much Ankit 🙂….🙏🏻
Thanks Aditya. It was much needed 🙂
Excellent Explanation... Wat a Class Sir ... Brilliant ...😍😍🤩🤩🤩
Keep watching💪
Very informative , explanation is easy to understand!!
Thanks bro 😊
Thank you Ankit your explanation is so simple
Thank you 😊
Hello Ankit, Really mind blowing explanation along with simple content. Very helpful.👌👌
Thank you 😊
i just love the way you teach
Thank you 😊
Your way of approach and explanation was excellent.
Glad it was helpful!
Nice way of teaching🙂
Keep watching
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
This is Amazing 👏
Thanks Shubham 😊
my god! awesome content
Thank you 😊
Thankyou so much sir for providing these videos on practical sql, please make videos on basics to advanced of CTE/Stores Procedures/Views also.
Sure
excellent
Thank you 😊
Fantastic.
Thank you! Cheers!
i asked my trainer but this solved my problem!! Thanks sir
Most welcome
Please add video on partition by multiple columns
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'.
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.
Good to hear that ☺️
When creating the tables
Why the columns and insert datais kept in the braces
Why it gives running sum only when we use order by?
As usual you nailed it .. can you create similar video for windows function zero to Hero …
Sure
Thanks so much Ankit. Can we have next video on joins? . It is much needed and would help in demystifying this topic.
I have a video ua-cam.com/video/fV8IYHOlONg/v-deo.html
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)?
@ankitbansal6 yes , please make video for above query
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.
It's there in the video 🙂
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.
Thank you so much. Sure will create the road map.
Hi sir can you create videos from scratch for beginners
Sir. Great Video. You said that you will tell the differences between normal running sum and unbounded preceding and current row.
Check this ua-cam.com/video/_GkPa7Iy2Fk/v-deo.html
@@ankitbansal6 Thank You Sir
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.
After watching your videos, SQL looks so much easier.
Glad to hear that!
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
I think it should be there in tips and tricks. I will check anyways.
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???
Lead/lag is meant for that then use that. It is just a work around.
@@ankitbansal6 Got It. Thank you 😊 🙏
which database you have used in this tutorial Ankit ji
SQL server
Can we seen something realted to performance tunings/ indexes in Tsql
Sure.
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 ?
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
Watch this
ua-cam.com/video/_GkPa7Iy2Fk/v-deo.html
Understood.. thank you sir 🙏
Is there any all video playly video of sql
Complex SQL Questions for Interview Preparation: ua-cam.com/play/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb.html
It would have been nice if you had explained with examples.
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... 🙏
What do you mean by within an hour. You are saying all the transaction should be picked up but they are accross multiple hours
@@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 ??
@@shubhammajhi7493 you can send me an email ankitbansal1988@gmail.com
@@ankitbansal6 okay thanks 🙂
Share sql playlist please
Complex SQL Questions for Interview Preparation: ua-cam.com/play/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb.html
CFBR
🙏
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
Send me problem on sql.namaste@gmail.com
Yes Brother. I am sharing the query.
@@ankitbansal6 Brother I have sent a demo data and query to mail. Please give me solution on it.
@@tazulislam2698 I have not got
@@ankitbansal6 Please check mail again. I have resent.
hi ankit 17/32
video completed
Keep watching
namasthey python course student, thanks for the SQL content, I appreciate your efforts didn't complete Python (start as well).
Sir Hindi me vedio banaye please and each step explain why you are doing in hindi
kuch samjh nehi aya bhai please try to explain why instead of saying the same thing in english
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);
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);
Thank you 😊