CTEs in MySQL | Advanced MySQL Series
HTML-код
- Опубликовано: 6 фев 2025
- Full MySQL Course: www.analystbui...
In this lesson we are going to take a look at CTEs in MySQL!
GitHub Code: github.com/Ale...
____________________________________________
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 Science - bit.ly/3Z4A5K6
📖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!
____________________________________________
BECOME A MEMBER -
Want to support the channel? Consider becoming a member! I do Monthly Livestreams and you get some awesome Emoji's to use in chat and comments!
/ @alextheanalyst
____________________________________________
Websites:
💻Website: AlexTheAnalyst.com
💾GitHub: github.com/Ale...
📱Instagram: @Alex_The_Analyst
____________________________________________
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
I have started your data analyst bootcamp. I have completed your entire sql, excel series with projects. Now i am learning Tableu. Still to go with power bi and python.
Thank you for bootcamp series
What SQL server do you use?
@@jameskorankye6096 postgre sql
@@jameskorankye6096 postgre sql
Hi, have a nice day. Hows going for you? IS there any updates or could you get a job? Thanks in advance
@@jameskorankye6096 postgre sql very simple to use
Using ctes constantly at my work, a senior colleague thought me once, they are very helpful for huge data sets to compare with each other
Data analyst freshers really wants you
Dont stop making videos
I have only experienced CTEs in Alex's RUclips lessons. I believe I will learn more when I use CTEs out in the wild. Reasonable pace with clear instructions. I feel challenged in Alex's courses but NEVER stupid. We all have to start somewhere and it's helpful to have someone showing me the way that has walked this path. Much gratitude for your efforts Alex. Cheers! 😎
Man, I'm really enjoying this series. I can't wait to make projects on SQLs. Thanks, Alex!
Thanks Alex, your course series helped me to best understanding about SQL. Clear, short, and precise.
Alex Freberg is one of the best Data Analysts out there.
Thank you Alex
Great lesson and the next one on #Temp tables will be too. Last week, one first round interview question (non technical part of the interview series) I was asked was "describe the difference between a CTE and a #Temp Table." Alex basically answered that in this video.... Gotta be ready to answer these types of questions on the fly. 🙂
How’d it go? Any update?
This is the best way to go on tables. Thank you for this information
Superb!!
I needed this as I'm currently learning CTE!!
😃😃😃😃Whaaaayyytttt .... 😭😭😭 this one is DIFFICULT
Hey Alex! Love the video on CTEs. I'm wondering if you'd consider making one on solving a gaps and islands problem? I've watched and read tutorials on them and the overview logic makes sense but I tend to get a bit lost when applying all the various window functions. Thanks!
Hello my friend. I love your content. You keep going
Thanks!
Hi Alex, the beginner and intermediate courses were presented on MS SQL platform while with the advanced you've moved to MySQL, which is written slightly different. I managed to find how to perform CTE in MS SQL, but it was kind of confusing. Anyhow, wanted to thank you for your videos. You're a great teacher and the videos are extremely beneficial.
Help help I've been stuck trying to run the query it keeps returning an ERROR:1146
TABLE CTE_Example doesn't exist
Thank you so much for another great video :)
Awesome! So cool. Thanks!
hi alex,
we need end to end project data analysis
please consider about it..............................................
How can I populate the data in the table? I need the values, can you add them? Thanks in advance
Thanks, very helpful and clear
why aren't the outputs grouped by gender in subqueries and CTEs?
very helpful
Can you explain me I have 2 different results for the same table: CTE AVG(salary) result 55589, without CTE 56090, if we export data to the spreadsheet it also shows AVG 56090.
just in case query without CTE:
SELECT AVG(salary)
FROM employee_demographics AS dem
JOIN employee_salary AS sal
ON dem.employee_id = sal.employee_id
After some investigation, I realized that the result obtained using the Common Table Expression (CTE) and grouping, as demonstrated in the video, corresponds to an average based on gender, resulting in 55,589. In contrast, the result without the CTE and grouping represents the average for all employees
Thanks you sir 👍
May I ask in which real-world scenario would getting an average of averages without weighing by their relative counts make sense? I know this was just a didactic example, but I was still a bit puzzled by that considering the fact that an actual population average would have to consider how many people are contributing for each average: ((avg_salary * count_salary) WHERE gender = female) + ((avg_salary * count_salary) WHERE gender = male) / the sum of both counts. Thanks in advance!
Thank you.
Alex why is my Azure DS rounding off the decimal numbers ? ever since I started the series it has been ignoring the decimal portion
2:32 WITH CTE_Example AS
(
SELECT gender, AVG(salary) AS Avg_salary, MAX(salary) AS max_salary, MIN(salary) AS min_salary, COUNT(salary) AS count_SALARY
FROM employee_demographics ed
JOIN employee_salary es
ON ed.employee_id = es.employee_id
GROUP BY gender
)
SELECT AVG(Avg_salary) FROM C
instead of 55589.2857000 am getting 55589
Hey folks,i combined the case Statement with this CTE to combine All tables then and use case of the case video
with Combined_Data as
(
SELECT dem.employee_id,dem.first_name,dem.last_name,dem.age,
sal.salary, sal.occupation,
dept.department_id,dept.department_name
FROM employee_demographics dem
INNER JOIN employee_salary sal
ON dem.employee_id = sal.employee_id
JOIN parks_departments dept
ON dept.department_id = sal.dept_id
)
select *,
case
when department_name = 'Parks and Recreation' and salary>=50000 then salary * 1.17
when department_name = 'Parks and Recreation' and salary=50000 then 1.07 * salary
when salary< 50000 then 1.05*salary
end as Bonus
from Combined_Data;
Sir can u help me with this project " Efficient multidimensional modeling for educational data analytics"I didn't find any videos related to this on yt😢😢
Alex what is the difference between VIEW function and this WITH CTE example stuff
✅
in previous videos you mentioning alias. but in this video, is it oke to not use "as"? i can directly type avg_sal etc?
Alias tanpa perlu ketik AS sama aja hasilnya. Misal AVG(salary) avg_salary, di mySQL terbaca AVG(salary) AS avg_salary.
@@YanuarYudha in practical life which method profesional using? using as or without as?
I have noticed that the result for avg salary for both female and male is different from the result obtained with windows functions, on the pervious video. Could anyone explain, please? which version is more accurate?
Hey, I see the same in both cases, female 53750 and male 57428.5714. Which results do you see?
Done
Can chat gpt do this?
easily, but it still does silly mistakes, so you gotta be able to verify the output
"Can you create a small single sample temporary data set and from that give me SQL query that joins two CTE's together?" plug this question into your CHAT GPT machine and see for yourself. As stated above it does make mistakes sometimes if not asked the question properly OR not given enough context.
does anyone know where I can find parks_departments table ?
I had to create one myself. Probably you do same
@@abdulqudusoyelami3019 you can download it by the link at the first SQL-video for beginners "installing mySQL" :)
Check his first video (select statement video) you will see the data set link in description
github
Can someone tell me where I am going wrong? I am trying to run SELECT AVG(avg_salary) FROM CTE_Example; right after the main query however, I keep getting the table does not exist? Even though I am trying to run it right after?
Had to get rid of the semi-colon right after the GROUP BY.
You have to make sure you have the parks_and_recreation database selected in the navigator section before running the query.
@@Mel-nd9fd I figured it out, but it seems that RUclips removed my own comment with the solution? WTF. I just had to remove the ; after GROUP BY as it was not the end of the entire query.