Intermediate SQL Tutorial | Partition By
Вставка
- Опубліковано 19 вер 2024
- Take my Full MySQL Course Here: bit.ly/3tqOipr
In today's Intermediate SQL lesson we walk through Using the Partition By. ____________________________________________
SUBSCRIBE!
Do you want to become a Data Analyst? That's what this channel is all about! My goal is to help you learn everything you need in order to start your career or even switch your career into Data Analytics. Be sure to subscribe to not miss out on any content!
____________________________________________
RESOURCES:
Coursera Courses:
Google Data Analyst Certification: coursera.pxf.i...
Data Analysis with Python - coursera.pxf.i...
IBM Data Analysis Specialization - coursera.pxf.i...
Tableau Data Visualization - coursera.pxf.i...
Udemy Courses:
Python for Data Analysis and Visualization- bit.ly/3hhX4LX
Statistics for Data Science - bit.ly/37jqDbq
SQL for Data Analysts (SSMS) - bit.ly/3fkqEij
Tableau A-Z - bit.ly/385lYvN
Please note I may earn a small commission for any purchase through these links - Thanks for supporting the channel!
____________________________________________
SUPPORT MY CHANNEL - PATREON
Patreon Page - / alextheanalyst
Every dollar donated is put back into my channel to make my videos even better. Thank you all so much for your support!
____________________________________________
Websites:
GitHub: github.com/Ale...
____________________________________________
All opinions or statements in this video are my own and do not reflect the opinion of the company I work for or have ever worked for
You know it's serious when the thumbnail has the suit
😂😂
Now I'm only looking for Suit in the thumbnail when I am in the mood of learning something 😅
Hahahahahahaha
Thank you, Alex. This is the best explanation of GROUP BY statement, for me: "The GROUP BY statement is going to reduce the number of rows in our output by actually rolling them up and then calculating the sums or averages for each group."
You make things so easy to understand.
you rewords it into an easy to digest sentence
Thank you so much Alex! Best tutorials on SQL by far. No wasting of time. Straight to the point. I learnt so much in just a couple of hours.
The first time I hear of Partition Over was yesterday when I was going through portfolio project #1, I'm glad to find a tutorial by you that explains it, Thank you, Alex! Great content!
"We have to group by all of the selected columns" .... This is the first time I hear this life-saving statement ... I have been getting this error many times for months and could not discover this myself nor did I heard it ever from any tutuor!
Congrats for the new subscriber = Me!
At this point I'm just gonna spam your videos with comments anyway, but again excellent video Alex. Know in your heart that your videos help others like me who wanna switch career to the data field by a lot! Your videos have amazing content, well organized, concise and have saved me a lot of time compared to watching 3 hours long tutorials. May life treat you well as it should!
I appreciate it! Thanks for watching :D
Simple, clear, and excellent content as always!
Thanks man! :D
Example:
AVG(Salary) OVER (PARTITION BY CompanyDepartment)
First, look at the "PARTITION BY CompanyDepartment." This means to find all the possible groups in that column, CompanyDepartment. So let's say that all the possible groups are Sales Department, Marketing Department, and Engineering Department.
Now, take a look at the "AVG(Salary)." This means that for each group (which we found above), find the AVG(Salary) for that group. For this example, this means that for the Sales Department, find the AVG(Salary) for that department. And same for the Marketing Department and Engineering Department.
This is my thought process for how to think of PARTITION BY, but everyone has their own thought process, so if your way works, then it works!
I hope this helps!
its probably more useable in some scenario than COUNT(Gender) OVER(PARTITION BY Gender). love your brief explanation of how using OVER(PARTITION BY COLUMN_NAME)
it helps my friend GBU
Thanks for this! Really helpful
Tnks for ur cmnt ,it was very grateful
On my journey through the boot camp...
Understanding the GROUP BY statement is becoming more clear as it is compared to other statements. Thank you.
This course should be included as the perfect practical Visualisation of SQL in SSMS
Appreciable👏👏
Two years ago but still educative. Thank you, Alex.
You're a great teacher. I would totally love a full SQL course from you one day. Going through the videos is very edifying. Thank you.
I stumbled across your videos and I must say that they are the best. I like the chunking. You have the voice (tone, volume, etc) that makes it comfortable to listen to. I finally got to topics that were hard for me to understand but you break it down so eloquently. Thanks you for what you have and are doing. I want to better develop my skills as a Data Analyst and you are assisting in that.
OMG! This is a huge help! I no longer have to join same table twice! Thank you!! :))))
You’re most welcome! Glad it was helpful 😁
جزى الله الشدائد كل خير *** وان كانت تغصصني بريقي
وما شكري لها حمدا ولكن *** عرفت بها عدوي من صديقي
just would like to thank you, the way i thank great guys like you in my language "Arabic". i have done the beginners tut and just completed the intermediate. it cannot be easier than the way you have explained it. once thanks a ton and wish you the best of luck
wow, super useful. I didn't know this and I would create temporary table and then join them by gender to get the output you mentioned here, but from now on I know Partition :) . Thank you so much!
That’s definitely a way to do it too!
@@AlexTheAnalyst Wonder what next videos are? Maybe more window functions?
An even more useful analysis here can be done using AVG(Salary) instead of COUNT(Gender). This will give the average salaries of each gender which can be compared to each other and can also be compared to each individuals salary wrt their gender.
i cant express how easy this was to understand. Thank you Alex so much
Short ,Crisp and Concise ! 🔥
Always love watching your videos!!!!
Thank you! Always glad to hear it 😁
Hi, I am still in the initial stages of Data Analytics, but this was very easy to understand. Thank you Alex 🤠
That's great! You're most welcome my friend!
Finished the course yet?
Thanks for the course, Alex. I'm working through for the first time having no prior experience. Playing around I calculated the average pay by gender and how far each employee is from the average using partition by and am completely amazed. This is really cool! You've done a great job laying it out.
Thank you so much for showing what the difference is. I'm working on a project using ChatGPT, and it used a partition by line. I had no idea what it was but you cleared it up!
Aaawwww yes! Last night I literally started learning SQL. I picked a couple Udemy courses from the ones you previously recommended and bought them for cheap on Cyber Monday.
Thanks for all you do!
That's great! Their $10 sale was nice! I picked some up myself haha
clear demonstration with beautiful voice
I used to use (pre SQL 2005) UDFs to do this inline (in the olden days). Wish I was paying attention when this feature was added - it would have saved a lot of time! Thanks for this great explanation!
I must Say Alex your Course is awsome , i just Started from Scratch , it is really really good
Well explained. Thanks Alex! You hit the nail right on the head by explaining the 2 examples in the video.
you are an amazing teacher.Couldnt believe how much i have learnt from you over these courses.im binging on your sql tutorials
DAY 3 of taking this course, and I finished Intermediate level SQL, thank you
Parton By and Window Functions has got to be the coolest thing I learned in SQL thus far. No joke
I use it quite often - super useful!
this might be the best SQL video I have ever watched
Good job alex, Just completed your basics and intermediate playlist. I have SQL as a chapter in middle school.
I think we are almost on the same pace. Do you not mind if we could be buddy. We could share knowledge and drag each other sometime. I am just a first time learner and I am determined to become a Data Analyst.
simple example, perfect demonstration. Thank you, Alex!
now that's why he's the goat the goat.
Alex! Really looking forward to the advanced tutorials!! Thanks much ! 🙂
I have a few advanced ones out! More coming out soon!
Amazing explanation and to the point. I will look into your other tutorials. Just needed this in combination with row number to solve a problem.
Las Vegas, Nevada, USA: Wow! This is so cool. Thanks making it clear!
Hey Alex, I love it! Glad to see that you used The Office (TV Series) cast names ;)
The best show :D
Thank you Alex . for the first time i have understood the use of partition by
Very useful and you explain really well, thank you as always!
Loved The Office data.
Simplest explanation ever!!
short, précised, and excellent explanation.
Being an old dog a bit seeing partition by would have been a life saver when using many group by and unionid and joined tables.
Thank you soo much for making queries understanding in easier way. Keep posting more stuffs like this to help & motivate us in every step of building queries.
Another great video Alex :)
I have recently completed learning path of SQL for Data Science in LinkedIn Learning.
What would you suggest i do next to further enhance my SQL skills?
Also, thank you for connecting on LinkedIn
Practice, practice, practice.
Basically the difference between summarise or mutate in R (or count/add_count)
Excellent and very clear, thank you!
Best tutorial ever!
Would love to see more videos on Window Functions.
Unless posted elsewhere might be a decent idea to show more about window functions and how you can even use it things like having rolling counter or much more IMO.
Thanks so much! Way better than Data Camp! :)
Thanks for explaining so well
Whoa!! I had no idea you can do this with PARTITION BY, all this time I've been using subqueries to get this result.
I Just Finish The Intermediate SQL Tutorial, And Now I Write Every Single Word With A Capital Letter Just Like SQL Query: D
love the series
Glad to hear it!
See, Partition by would have been REALLY great to know when I was trying to get a "total sum" of a given inventory item that was broken down by lots in a location. For example. Item 123 could have lot a, lot b and lot c in 1 location. there might be 2 in one lot, 3 in another and 5 in another. My solution was to subquery the "Sum all items in a location" then "Inner join" that sum and join on the Item Location (as that will always be unique)... so instead of having a really large query, this partition by would have shrunk the query down to an easy to read "Look here is the items, their locations and total sum of all the lots in each location..
Well.. better late than never.
Thx man!
WHATS THE DIFFERENCE BETWEEN CHAR AND VARCHAR
Thanks Alex, great tutorial.
Had to watch this a few times to let it sink in, but great job, thanks for a simple example!
Hi Alex,
When you explain the last query at the end, the one with aliasing to abbreviate the table names, in that query you use a Left Join. . What is the Left Join returning to us? Don't you mean Left outer join?
Well presented and useful. Thank you Alex.
it was a great way to compare partition by and group by. tnx
So great to hear! :D
you are awesome, keep doing videos please!
This is super helpful Alex! Thanks a lot!
You’re very welcome! Glad it was helpful 👍
this one is great. it's very similar to a fixed calculation from tableau
Nobody's gonna talk about "The Office"? Toby is making 50000 No God No No NOOOOOOOOOOO
oh man this is a very good explanation
Great lessons, thx Alex
Cant stop thanking you alex
Oh thanks men👍. Nice explained with comparison to group by🙂
Simple and clear. Thank you !
thanks for this.
Good explanation .... thanks!
This makes sense, so partition by just allows us see everything spread out, instead of the GROUP By.
Question, will we still need to use "HAVING" Instead of "WHERE" ?
I'm just asking this to help other viewers curiosity lol
Thanks for such a well explained content. I was able to visualize and understand clearly.
Your contents have given me direction in my journey to being a Data Analyst. Thank you Alex. I was thinking, what's the difference between the PARTITION and ORDER BY?
would it be possible to achieve the same result using an ORDER BY Gender ASC, function after the "ON" clause?
So is the "dbo" not needed in general when referring to the tables? Dipping my toes in SQL and these videos are really helpful, much appreciated.
Hey Alex I'm currently working as SQL Dev for US healthcare insurer. I basically write complex SQL queries as per given Requests and pull data and send it ...
I'm just curious what I have to do next in order to enhance my career.. I hope you read this comment.. Waiting for your response.
I would probably look at Temp Tables, Stored Procedures, and automating tasks in SQL. That would definitely enhance your career. Good luck!
Thank you!
I watched this video one more time...
For me OVER (PARTITION BY) was a new statement - therefore I searched for a little bit more information about it...
I need to admit it's a good tool to manipulate data.
P.S. For some reason when I watched this video first time I missed your explanation of "OVER" power. It was a good idea to rewatch video one more time 😀
I am looking forward to the advanced videos
Great video thank you
Great Video, Thanks :) !
Thanks, Alex!
Great video, very comprehensive!
Realy Thnk you sir for your service Loved it.
Thank you so much @Alex
Makes sense, thanks Alex!
Can you please do a tutorial on self-join
You always remind me of the Tim Ferriss of SQL
thank you sir , i appreciate your hard work
awesome lessons
Thank you, Sir. All Good to you
Great tutorial as always
Thank you!
Please make a video on how to create sql reports and visualization on Excel.
I will definitely be doing that in the future 👍
Thank you 😊
Thanks! Help a lot!
Hi Alex, thank you so much for your videos, you are a life saver. I was wondering: why don't you add the aliases to the beginning of the SELECT statement? (ie. dem.FirstName, dem.LastName, dem.Gender?) TIA! Or if anyone else could help me answer this question that would be great.
Lucky video 13 piqued my interest.