Intermediate SQL Tutorial

Intermediate SQL Tutorial | Partition By

Рет қаралды 276,209

Күн бұрын

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

Пікірлер: 200

@pana71 3 жыл бұрын

You know it's serious when the thumbnail has the suit

@AlexTheAnalyst 3 жыл бұрын

😂😂

@Thedeepanshu95 3 жыл бұрын

Now I'm only looking for Suit in the thumbnail when I am in the mood of learning something 😅

@judugar 9 ай бұрын

Hahahahahahaha

@AkporHari Жыл бұрын

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.

@saniagus1 Жыл бұрын

you rewords it into an easy to digest sentence

@austin16377 Жыл бұрын

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!

@nyabudulu Жыл бұрын

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)

@ailoveu100 10 ай бұрын

it helps my friend GBU

@cynthiaanike3715 8 ай бұрын

Thanks for this! Really helpful

@mayankdewli1010 2 жыл бұрын

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.

@AmanyEzzat 3 жыл бұрын

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!

@scorpion7434 Жыл бұрын

"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!

@edricnes5882 2 жыл бұрын

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!

@AlexTheAnalyst 2 жыл бұрын

I appreciate it! Thanks for watching :D

@AndrewMoMoney 3 жыл бұрын

Simple, clear, and excellent content as always!

@AlexTheAnalyst 3 жыл бұрын

Thanks man! :D

@oseikwamejones9019 Жыл бұрын

Two years ago but still educative. Thank you, Alex.

@1689JeffChavez Жыл бұрын

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.

@lucad6649 2 жыл бұрын

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.

@andieru6760 2 ай бұрын

this might be the best SQL video I have ever watched

@adityashenoy2573 3 жыл бұрын

Short ,Crisp and Concise ! 🔥 Always love watching your videos!!!!

@AlexTheAnalyst 3 жыл бұрын

Thank you! Always glad to hear it 😁

@abhinendrsharma8137 3 жыл бұрын

This course should be included as the perfect practical Visualisation of SQL in SSMS Appreciable👏👏

@lemontea000 3 жыл бұрын

OMG! This is a huge help! I no longer have to join same table twice! Thank you!! :))))

@AlexTheAnalyst 3 жыл бұрын

You’re most welcome! Glad it was helpful 😁

@awimbley1336 Жыл бұрын

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.

@MrRubyElf 7 ай бұрын

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!

@jonathannali7682 3 жыл бұрын

Hi, I am still in the initial stages of Data Analytics, but this was very easy to understand. Thank you Alex 🤠

@AlexTheAnalyst 3 жыл бұрын

That's great! You're most welcome my friend!

@phebeaustin8474 Жыл бұрын

Finished the course yet?

@harrisliao2509 3 жыл бұрын

clear demonstration with beautiful voice

@medimed-su3ir 4 ай бұрын

I must Say Alex your Course is awsome , i just Started from Scratch , it is really really good

@lisaamante5384 3 жыл бұрын

Las Vegas, Nevada, USA: Wow! This is so cool. Thanks making it clear!

@jacobmark8810 3 жыл бұрын

Parton By and Window Functions has got to be the coolest thing I learned in SQL thus far. No joke

@AlexTheAnalyst 3 жыл бұрын

I use it quite often - super useful!

@iraklisalia1533 3 жыл бұрын

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!

@AlexTheAnalyst 3 жыл бұрын

That’s definitely a way to do it too!

@iraklisalia1533 3 жыл бұрын

@@AlexTheAnalyst Wonder what next videos are? Maybe more window functions?

@hta-bi249 2 жыл бұрын

Well explained. Thanks Alex! You hit the nail right on the head by explaining the 2 examples in the video.

@rnjesus9950 9 ай бұрын

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.

@gulhermepereira249 6 ай бұрын

simple example, perfect demonstration. Thank you, Alex!

@helmybinmadi5776 Жыл бұрын

جزى الله الشدائد كل خير *** وان كانت تغصصني بريقي وما شكري لها حمدا ولكن *** عرفت بها عدوي من صديقي 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

@kaikai5356 2 жыл бұрын

you are an amazing teacher.Couldnt believe how much i have learnt from you over these courses.im binging on your sql tutorials

@12meeth34 Жыл бұрын

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.

@prithvipradip8467 3 жыл бұрын

Alex! Really looking forward to the advanced tutorials!! Thanks much ! 🙂

@AlexTheAnalyst 3 жыл бұрын

I have a few advanced ones out! More coming out soon!

@nankundabridget9783 9 ай бұрын

Thank you Alex . for the first time i have understood the use of partition by

@davestorm6718 2 жыл бұрын

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!

@cinemacolors Жыл бұрын

Hey Alex, I love it! Glad to see that you used The Office (TV Series) cast names ;)

@AlexTheAnalyst Жыл бұрын

The best show :D

@viktoriaballa4622 11 ай бұрын

DAY 3 of taking this course, and I finished Intermediate level SQL, thank you

@DataScience111 2 жыл бұрын

short, précised, and excellent explanation.

@patrickmc8779 2 жыл бұрын

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.

@jonathanbrotto7278 2 жыл бұрын

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.

@itsReshad 2 жыл бұрын

Very useful and you explain really well, thank you as always!

@riteshpillai4865 2 жыл бұрын

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.

@zidanhussain8092 2 жыл бұрын

Good job alex, Just completed your basics and intermediate playlist. I have SQL as a chapter in middle school.

@Oluwagbeoyemiga 2 жыл бұрын

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.

@abdallahdikko8137 10 ай бұрын

now that's why he's the goat the goat.

@sebulva 8 ай бұрын

Loved The Office data.

@kstevens0915 3 жыл бұрын

Simplest explanation ever!!

@joshhayes3433 2 жыл бұрын

Had to watch this a few times to let it sink in, but great job, thanks for a simple example!

@adril.857 2 жыл бұрын

Thanks so much! Way better than Data Camp! :)

@MethodOverRide 3 жыл бұрын

Would love to see more videos on Window Functions.

@yoanperez172 2 жыл бұрын

Excellent and very clear, thank you!

@eliz964 3 жыл бұрын

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!

@AlexTheAnalyst 3 жыл бұрын

That's great! Their $10 sale was nice! I picked some up myself haha

@djangoworldwide7925 6 ай бұрын

Basically the difference between summarise or mutate in R (or count/add_count)

@nafsahmad5736 2 жыл бұрын

Best tutorial ever!

@anthonynkem 2 жыл бұрын

Thanks Alex, great tutorial.

@noronhasanjay 2 жыл бұрын

Well presented and useful. Thank you Alex.

@user-lx7tn6tt8b 6 ай бұрын

Thanks for explaining so well

@ManiSh-jl8ej Жыл бұрын

I Just Finish The Intermediate SQL Tutorial, And Now I Write Every Single Word With A Capital Letter Just Like SQL Query: D

@rheamercadal6534 3 жыл бұрын

This is super helpful Alex! Thanks a lot!

@AlexTheAnalyst 3 жыл бұрын

You’re very welcome! Glad it was helpful 👍

@dominikranters1346 3 жыл бұрын

Oh thanks men👍. Nice explained with comparison to group by🙂

@Michael20 2 жыл бұрын

this one is great. it's very similar to a fixed calculation from tableau

@user-hg7rr1yh8o Жыл бұрын

Whoa!! I had no idea you can do this with PARTITION BY, all this time I've been using subqueries to get this result.

@mahmoudferhat8031 Жыл бұрын

Simple and clear. Thank you !

@deepikagoyal07 Жыл бұрын

Thanks for such a well explained content. I was able to visualize and understand clearly.

@alirezaghanbarzadeh1679 3 жыл бұрын

it was a great way to compare partition by and group by. tnx

@AlexTheAnalyst 3 жыл бұрын

So great to hear! :D

@krishnabarfiwala5766 2 жыл бұрын

love the series

@AlexTheAnalyst 2 жыл бұрын

Glad to hear it!

@saljeshmaharjan6760 Жыл бұрын

Cant stop thanking you alex

@seekerj1643 2 жыл бұрын

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.

@billumbaggins3288 Жыл бұрын

oh man this is a very good explanation

@MPN016 Жыл бұрын

you are awesome, keep doing videos please!

@BharatiyaFirstThanHinduMuslim 8 ай бұрын

Good explanation .... thanks!

@raulmartellenriquez3712 Жыл бұрын

Great lessons, thx Alex

@shokoofehheidari5345 Жыл бұрын

Simple and useful. Thanks a lot!

@princechukwuemeka1947 3 жыл бұрын

WHATS THE DIFFERENCE BETWEEN CHAR AND VARCHAR

@tyroneogarrojr.453 Жыл бұрын

Great video, very comprehensive!

@Venky_Himalay 2 жыл бұрын

Realy Thnk you sir for your service Loved it.

@ltcmdc1782 Жыл бұрын

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.

@pramodduvvuri7045 3 жыл бұрын

Nobody's gonna talk about "The Office"? Toby is making 50000 No God No No NOOOOOOOOOOO

@wildgorilla1205 Ай бұрын

Thx man!

@michaelsalgado-e5q 25 күн бұрын

You always remind me of the Tim Ferriss of SQL

@sonabhatia4295 3 жыл бұрын

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

@houstonvanhoy2198 2 жыл бұрын

Practice, practice, practice.

@krishnabarfiwala5766 2 жыл бұрын

thanks for this.

@ayaqz3144 11 ай бұрын

thank you sir , i appreciate your hard work

@maximillianquaife-larsen3799 5 ай бұрын

Great video thank you

@user-cy4qx4yd9h Ай бұрын

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?

@mubashiraqeel9332 3 жыл бұрын

I am looking forward to the advanced videos

@romanvasiura6705 2 жыл бұрын

Thank you!

@romanvasiura6705 2 жыл бұрын

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 😀

@pramudyadika_ Жыл бұрын

Thanks, Alex!

@tagoreji2143 Жыл бұрын

Thank you, Sir. All Good to you

@zeta9882 25 күн бұрын

I have a doubt, why there is no dbo in the middle?

@chrisomoha636 9 ай бұрын

awesome lessons

@moisesdiaz9852 3 жыл бұрын

Great tutorial as always

@AlexTheAnalyst 3 жыл бұрын

Thank you!

@samartajshaikh2601 Жыл бұрын

Didn't understand your example, can you suggest me some supplement reading to be able to understand partitioning (and your video on it) a little better.

@untangledyogi4864 Жыл бұрын

Thank you so much @Alex

@maphindelamk2261 9 ай бұрын

Hi Alex. I have been practicing the Case Stements while learning I get an error while running this query. It says error next to CASE SELECT * CASE WHEN Salary > 50000 THEN 'High Class' ELSE 'Low Class' END FROM EmployeeSalary

@nicolaemariusmarcu6569 Жыл бұрын

Can somebody explain why, when I use partition by, is returning only 2 values from the list and not the all of them?

@anthonyd9043 Жыл бұрын

Makes sense, thanks Alex!

@woltron4o 3 жыл бұрын

Great Video, Thanks :) !

@Gokulsai18 2 жыл бұрын

The office reference was nice

@kamaur01 Жыл бұрын

Lucky video 13 piqued my interest.

@cindystokes8347 Жыл бұрын

At :40 did he have some shortcut to bring up those queries? Or was that just a jump scare? I tried the shortcut from the Object Explorer and that's not what it brings up.

@smokystar6701 3 жыл бұрын

Can you please do a tutorial on self-join

@kunlemaxwell 2 жыл бұрын

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?