I've seen so many videos trying to explain windows functions and none of them Explain it as you do. I think the difference is you showed the difference between using group by, CTEs and windows function. Thanks a lot, Dev.
I came to CZcams after going through a paid online SQL course wanting to understand window functions better. You explained/showed it to a level where conceptually it sunk in. Thanks.
A short time ago I was contacted by an employer for an SQL Developer job (Entry Level), and they wanted me to take a skills assessment. The manager told me that I should know CTE's and Window Functions. I was pretty comfortable with CTE's but I basically learned what Window Functions were (and how they are used) from your videos, and also found your CTE videos a useful refresher. I watched the series over a few times until I was comfortable playing around on my machine with them, and, turns out, like 90% of the assessment was a practical coding assessment for Window Functions/CTE's (moderately more complex than the examples in your videos). I ended up being hired for the position. I just wanted to say that I appreciated your videos and they were apparently clear enough to both introduce Window Functions and help me understand it enough to apply it appropriately (in tandem with playing around a bit with a practice database i.e. AdventureWorks). So thanks!
That’s absolutely fantastic news, congratulations on the position. It’s great to hear you took the concepts from the videos and were able to apply it to different databases and answer questions, that’s exactly what I was hoping for when I set up the channel. Keep asking questions and learning and you will go far.
In the last bullet of your "Window Functions" slide, you want to use the preposition "into" rather than the phrase "in to". The result set is being split into partitions, denoting that the abstract positioning of the data is changing.
Wow! I've literally been searching for an easy-to-understand explanation of what Window Functions are, and this one is the best explanation so far. Thank you so much!
I have watched this video, and others (in fact all of them), of yours and really enjoy your approach and pace. For what it's worth, I have watched many other SQL Tutorial channels, however, I find myself drawn back to your videos time and again.
Awesome video, thanks! The explanation is better than some paid online courses provide. It would be great if you also shared the table you use for querying to be able to practice and compare the results.
Clear and logistic! I find a lot of videos about introducing window functions, but this is the best one and expound some relations between other functions!
I've always used the partition inside the over, so just using the over alone is pretty cool! A fellow developer I know uses the case statement. Very versatile, and much better than CTE's, IMHO.
That’s an interesting approach with CASE statements, what if you wanted partition by CustomerId and had 100s of customers or partition by multiple columns surely the approach would become way too long winded.
Please do work along projects for data analysis or business intelligence from start to finish. It'd teach how to do basic projects from start to finish. And would help in updating skills as well
Best explanation for Window functions! Can you please zoom in onto the area where you are writing the code, I listen to your videos on the go on my phone and it's not always easy to see what's being written on a mobile screen
Great video! If you could make some videos with Problems where we would be using these Analytical functions, that would help the people preparing for interviews. And would help in better understand the topic.
I’m not sure what currency that’s in but I’m guessing it’s a lot, I see a lot of training providers are in it for the money and don’t deliver good content. It’s part of the reason I set up the channel, more people are needed in the world of data and analytics and training should be readily available. Microsoft offer a lot of free training through Microsoft Docs and Microsoft Learn and edX are also quite good.
One feedback. next time when you record your videos try increasing the font size. I'm trying to watch this video on iPhone and its impossible to see the text on the screen. Why not increase the font size? you have 99% white space on the screen.
Wonderful video. Is there a way to created window functions in “Views” - where you can see the tables and fields being used? All while still performing a window function
thanks for speaking english, mr bearded dev i always have to skip when they start talking in hindi with some mispronounced english words in there. way too many videos of that kind exist..
It is important to notice that the Sales_Date column in your DB has its value all truncated by days. So, if someone wants to do that with a more granular level of "datetime", this someone should learn how to truncate dates.
I wouldn’t use the term truncate personally, the Sales_Date column is a data type of DATE which has a granularity of day therefore you are correct if you are using a DATETIME data type then you will need to remove the time element and for that I would recommend CAST or CONVERT. I generally only use the term truncate when removing characters from a string or removing all rows from a table but I’m aware other RDBMS might refer to the operation as truncate.
Appreciate the feedback, I have enlarged the queries on my latest videos so hopefully this is an improvement. I am also looking in to some software that will allow zooming in to show everything more clearly on all devices.
Conditional window functions come to mind, like max(case when)over(...). as opposed to case when max()over(). They are very particular for sure. but its not something that I find well explained in many literature. But mostly the former is of interest.
Sorry could anyone here help me out with the question about Window Functions, that: it's already include the ORDER BY, so is it necessary to explicit again when we back to the main query ORDER BY again. For example: SELECT sales_transaction_date, SUM(total_sales) OVER (ORDER BY sales_transaction_date) FROM sales ORDER BY sales_transaction_date;
Yes, the order by within the windows function does not impact the order of the results even if it seems to do so. You must add an order by at the end of the query to guarantee ordered results.
Can we use where clause or any kind of filters in any way for each windows function seperately ? We are unable to fully utilize window function without where clauses... or any trick to do so in sql server ?
Unfortunately not, window functions will run over the entire set of data. There are a couple of options, one is to build separate window functions and then join the results using derived tables or CTEs, this has the problem with running over the same data multiple times, the other option is to get creative with case statements, could potentially flag rows for whether they should be included in calculations or not, this isn't that straightforward though depending on what you want to achieve.
Unfortunately I don't have it, but if you take a look at this video, czcams.com/video/lBcDSsgp0RU/video.html, the description includes the code to follow along.
not working over function in derby or sqlite getting error " [Exception, Error code 30,000, SQLState 42X01] Syntax error: Encountered "PARTITION" at line 2, column 22." SELECT SALES_ID,SALES_DATE,ITEM,PRICE,QUANTITY,LINE_TOTAL, SUM(LINE_TOTAL) OVER(PARTITION BY SALES_ID) AS SALES_TOTAL FROM SALES_DETAILS;
For this line , SUM(Line_Total) OVER() AS Total what is the point of adding the over clause? Is this the same as doing the sum(line_total) and then adding group by at the end? Is over() allowing us to skip the group clause? And is partition by essentially equivalent to group by when doing aggregate functions?
OVER() is used to define the window, as it’s blank all rows are considered. It will return the same value as grouping but with window functions we can retain the detail rows. PARTITION BY is part of the OVER clause and further defines the window, this is different from grouping, with grouping we lose the detail rows, with window functions we retain the detail. It is important to understand that grouping is evaluated before window functions, which are usually part of the SELECT clause but can be used in ORDER BY too. You can use grouping and window functions in the same query. You can find more information on logical query processing here: m.czcams.com/video/sBRfBU5jh18/video.html
Great Video, Thanks! How to we select multiple conditions on the OVER part. e.g, Order numbers got repeated every day and we had to partition over 'day' AND 'order number' to get to the order total. Is this possible?
Hi, I will put this up as soon as I can. Thanks for the feedback on the videos, let me know if there is a particular area you would like to see a video on.
Hi Phung, unfortunately I no longer have the script for it but because of that I have made another video where you can follow along: czcams.com/video/lBcDSsgp0RU/video.html
@@BeardedDevData yeah, i'm using these functions for the first time and need to join multiple tables and use the right table info in the partition for different columns. Eg count and partition from table b for one column and do the same from table c for another. Just wondering if you had covered this at all. I've tried a few tests but with different results. I'm doing this in Salesforce marketing Cloud so its not a full sql db environment, so that might be why. Its sql server 2005.
Because the window function happens in SELECT or ORDER BY phases you cannot filter in the WHERE clause in the same statement. To do this you need to use a derived table, SELECT * FROM ( ) AS D WHERE Line_Total >= 3
What if you wanted get the Sales_Details_ID on the row with an aggregate value (say MAX(Line_Total) ) within a Sales_Id group? So I want to look at each Sales_Id group, and get the Sales_Details_Id on the row with the maximum line_total. I've been looking for a way to do this without a CTE but that might end up being the only way, CTE + RowNumber() partition by sales ID
So what I want is something like SELECT Sales_Details_ID, MAX(Line_Total) FROM Sales_Details GROUP BY Sales_ID (This wouldn't work however because Sales_Details_ID would have to be in the GROUP BY clause
Can you tell me the relationship between sale_id and sales_details_id? The reason I ask is that if one sale_id can have many sales_details_ids related then when you group by sale_id which of the sales_details_ids would need to be returned? As one value can only be returned then you need an aggregate function on sales_details_id. If it’s a one to one relationship then you can group by sales_details_id.
@@BeardedDevData So in my case it's EmployeeID, EmployeeReviewScore, and ReviewDate. The relationship is one EmployeeID to many EmployeeReviewScore and ReviewDate. What I want is the most recent EmployeeReviewScore for each employeeID. So I would need to do something like this: SELECT EmployeeID, EmployeeReviewScore, MAX(ReviewDate) FROM table GROUP BY EmployeeID I only want to group by EmployeeID, but SQL Server syntax requires you to group by ALL non-aggregate fields. If I add EmployeeReviewScore to the GROUP BY clause, then I'll get multiple rows for each EmployeeID, which I don't want. I've solved this by creating a CTE ordered by Date with a rownumber() partition and joining to that, but I was just wondering if there is a cleaner way I can do this without a CTE.
In the scenario you have explained a CTE is the perfect choice, to get the result you require you need to perform multiple operations on the data, first identify the MAX(review date) and then use that to identify the employee review score. Of course there are other ways to do it using other types of temporary objects but I like to use CTEs for this exact situation. In fact when somebody asks me what a CTE is and what’s it’s used for I would describe this exact problem.
Good video. Thanks. Can we use where clause on aggregate function (eg: select count(*) over(partition by department_id order by department_id)no_emp ,e.* from employees e where count(*) >3;
Unfortunately you can’t within the WHERE clause as this is evaluated before the aggregations are applied. If you need to filter based on an aggregate function you have the option of HAVING COUNT(*) > 3 if using GROUP BY or use a CTE and then adding a WHERE clause when selecting from the CTE.
Hi, unfortunately I no longer have the database but as so many people have requested it I’m going to upload a new copy that people can follow along to. I will upload it this weekend.
I loved this video, looking forward to see more videos from you
I've seen so many videos trying to explain windows functions and none of them Explain it as you do. I think the difference is you showed the difference between using group by, CTEs and windows function. Thanks a lot, Dev.
Agreed
I came to CZcams after going through a paid online SQL course wanting to understand window functions better. You explained/showed it to a level where conceptually it sunk in. Thanks.
That’s great.
A short time ago I was contacted by an employer for an SQL Developer job (Entry Level), and they wanted me to take a skills assessment. The manager told me that I should know CTE's and Window Functions. I was pretty comfortable with CTE's but I basically learned what Window Functions were (and how they are used) from your videos, and also found your CTE videos a useful refresher. I watched the series over a few times until I was comfortable playing around on my machine with them, and, turns out, like 90% of the assessment was a practical coding assessment for Window Functions/CTE's (moderately more complex than the examples in your videos). I ended up being hired for the position.
I just wanted to say that I appreciated your videos and they were apparently clear enough to both introduce Window Functions and help me understand it enough to apply it appropriately (in tandem with playing around a bit with a practice database i.e. AdventureWorks). So thanks!
That’s absolutely fantastic news, congratulations on the position. It’s great to hear you took the concepts from the videos and were able to apply it to different databases and answer questions, that’s exactly what I was hoping for when I set up the channel. Keep asking questions and learning and you will go far.
One of the best explaination of Window functions to get started on !!
In the last bullet of your "Window Functions" slide, you want to use the preposition "into" rather than the phrase "in to". The result set is being split into partitions, denoting that the abstract positioning of the data is changing.
Yes, that’s my mistake.
Wow! I've literally been searching for an easy-to-understand explanation of what Window Functions are, and this one is the best explanation so far. Thank you so much!
That's fantastic to hear.
I have watched this video, and others (in fact all of them), of yours and really enjoy your approach and pace. For what it's worth, I have watched many other SQL Tutorial channels, however, I find myself drawn back to your videos time and again.
Thanks, appreciate the support.
The best description of windows functions that I have seen. Thank you!
Thanks so much.
Awesome introduction to Window functions. Clear as Icelandic water on a sunny day. good job
Awesome video, thanks! The explanation is better than some paid online courses provide. It would be great if you also shared the table you use for querying to be able to practice and compare the results.
Explain window functions with examples (sum, count) very clearly. Thank you.
Great video. one of those topics as a software dev I never really cared to look into but glad you made it so simple to learn!
Short, convice and to the point. Very good video!
You are by far the best SQL tutor. I dont get it why you dont have many subscribers and views. Great videos.
Thank you very much.
Thank you so much, i was never able to understand these until now !
Clear and logistic! I find a lot of videos about introducing window functions, but this is the best one and expound some relations between other functions!
Thanks so much!
Thanks a lot the best explanation for OVER() and PARTITION BY I ever seen :)
Thanks so much.
Cooool ! I was confused by the concept and now I fully understand!
Another great tutorial from BeardedDev!!
Thanks so much.
Love your SQL tutorial videos! Help me a lot. Super clear and easy to understand.
Thanks so much 👍.
Very well explained video. I studied it for the first time and clearly understood it. Thanks!
thank you, thank you, thank you so much! you made this topic so easy to understand!!!
Thanks so much for the feedback.
yo I came here for window function but the axxxent is a def a bonus ;) love from the USA!
Thanks so much.
great example, very well explained. Thank you.
No problem 👍
Great tutorial thank you!
Done thanks took notes
Awesome video! Love that you explained the group by version and included both in the description of the video. Thank you!
Your explaining methods and skills are awesome. It helped me a lot. Thanks.
great explanation of window functions, thanks a lot
I've always used the partition inside the over, so just using the over alone is pretty cool! A fellow developer I know uses the case statement. Very versatile, and much better than CTE's, IMHO.
That’s an interesting approach with CASE statements, what if you wanted partition by CustomerId and had 100s of customers or partition by multiple columns surely the approach would become way too long winded.
Very good demo and explanation for windows functions.
Very well explained. Thank you.
Dude, great video, well planned, well explained, good audio, amazing work.
Thanks, let me know if there are any any particular tutorials you would like to see.
Absolutely mate. Maybe Git would be good hahahaha still a newbie
I was looking for Window Function in R and I got this. Anyway it was a great learning experience . For sure I gonna use it in the future.
That’s great.
love the video! window function well-explained! Thank you so much! also love your accent!!!
Thank you for the video. Excellent tutorial and explanation.
Some videos on the Statistical functions would be fantastic.
Hi, I will put them down on my to do list for the next couple of months.
Your tutorial is just so amazing!!
Thanks so much
Please do work along projects for data analysis or business intelligence from start to finish. It'd teach how to do basic projects from start to finish. And would help in updating skills as well
Best explanation for Window functions! Can you please zoom in onto the area where you are writing the code, I listen to your videos on the go on my phone and it's not always easy to see what's being written on a mobile screen
Thanks so much, appreciate the feedback, I have added zoom to my latest videos.
clearly understand! Thank you for uploading this tutorial
Thank you for the positive feedback, let me know if there is anything you would like me to do a video tutorial on
Great video!
If you could make some videos with Problems where we would be using these Analytical functions, that would help the people preparing for interviews.
And would help in better understand the topic.
Great idea, I will come up with some scenarios.
Thanks for sharing this amazing SQL tutorial!
No problem, more tutorials coming soon.
Good explanation...
Thank you :)
Great Video! I like the way you teach...
Thanks so much, look out for new videos coming soon
Great job explaining this concept!
Paid 10k in tuition only to have to resort to youtube for an actual easy to understand lesson.
I’m not sure what currency that’s in but I’m guessing it’s a lot, I see a lot of training providers are in it for the money and don’t deliver good content. It’s part of the reason I set up the channel, more people are needed in the world of data and analytics and training should be readily available. Microsoft offer a lot of free training through Microsoft Docs and Microsoft Learn and edX are also quite good.
Very good explanation
Awesome content
Need dataset for all your window functions video tutorials as you are using different dataset for different videos
A great tutorial, thanks!
Thank you. Awesome video
Beautiful!!!!!!
Brilliant tutorial, thank you.
Thanks so much.
great lectures.
Could you please share the dataset to practice along. Thanks !!
first time of seeing it, can decide to sub at the first time!
Great video! Thank you.
i'll have to watch peaky blinders before watching this
So helpful. Thank you!
Great Job. John Snow!
One feedback. next time when you record your videos try increasing the font size. I'm trying to watch this video on iPhone and its impossible to see the text on the screen. Why not increase the font size? you have 99% white space on the screen.
for an oldie like me, even watching on a PC, I cant read anything.
very nice.
❤ Thank You
No problem.
Wonderful video. Is there a way to created window functions in “Views” - where you can see the tables and fields being used? All while still performing a window function
You can create a window function in views but to see the table and columns being used you'd have to look at the definition of the view
thanks for speaking english, mr bearded dev
i always have to skip when they start talking in hindi with some mispronounced english words in there. way too many videos of that kind exist..
Awesome video, thanks!
Thanks for the positive feedback.
very well explained
Thanks for the positive feedback.
It is important to notice that the Sales_Date column in your DB has its value all truncated by days. So, if someone wants to do that with a more granular level of "datetime", this someone should learn how to truncate dates.
I wouldn’t use the term truncate personally, the Sales_Date column is a data type of DATE which has a granularity of day therefore you are correct if you are using a DATETIME data type then you will need to remove the time element and for that I would recommend CAST or CONVERT. I generally only use the term truncate when removing characters from a string or removing all rows from a table but I’m aware other RDBMS might refer to the operation as truncate.
@@BeardedDevData makes a lot sense. Thank you.
Any plans to do a couple of videos on the Windows statistical functions?
I can certainly do some, I'm just finishing off my DA-100 certification then I will be making a lot more videos
@@BeardedDevData thanks, they would be great.
Really hard to see your queries on mobile phone :(
mobile phone? not much better on a laptop!
Appreciate the feedback, I have enlarged the queries on my latest videos so hopefully this is an improvement. I am also looking in to some software that will allow zooming in to show everything more clearly on all devices.
BeardedDev thank you, fair play.
Thanks, let me know if there are any any particular tutorials you would like to see.
Conditional window functions come to mind, like max(case when)over(...). as opposed to case when max()over(). They are very particular for sure. but its not something that I find well explained in many literature. But mostly the former is of interest.
Can you make a video about GROUPING_ID
bearededlegend
Sorry could anyone here help me out with the question about Window Functions, that: it's already include the ORDER BY, so is it necessary to explicit again when we back to the main query ORDER BY again.
For example:
SELECT sales_transaction_date, SUM(total_sales) OVER (ORDER BY sales_transaction_date) FROM sales ORDER BY sales_transaction_date;
Yes, the order by within the windows function does not impact the order of the results even if it seems to do so. You must add an order by at the end of the query to guarantee ordered results.
@@BeardedDevData Oh, thank you very much (Y)
here's the full window function playlist for anyone interested: czcams.com/play/PLgR-BOYibnN0QqIPFbMlS01bw8x9g07Ll.html
Can we use where clause or any kind of filters in any way for each windows function seperately ?
We are unable to fully utilize window function without where clauses... or any trick to do so in sql server ?
Unfortunately not, window functions will run over the entire set of data. There are a couple of options, one is to build separate window functions and then join the results using derived tables or CTEs, this has the problem with running over the same data multiple times, the other option is to get creative with case statements, could potentially flag rows for whether they should be included in calculations or not, this isn't that straightforward though depending on what you want to achieve.
Can you share the link to the database info so we can connect as well.
Unfortunately I don't have it, but if you take a look at this video, czcams.com/video/lBcDSsgp0RU/video.html, the description includes the code to follow along.
Hello. Can I get the Data as I wish to practice it simultaneously
Hi, please have a look at this video, m.czcams.com/video/lBcDSsgp0RU/video.html, included in the description is the code to create the table.
USEFUL
not working over function in derby or sqlite getting error "
[Exception, Error code 30,000, SQLState 42X01] Syntax error: Encountered "PARTITION" at line 2, column 22."
SELECT SALES_ID,SALES_DATE,ITEM,PRICE,QUANTITY,LINE_TOTAL,
SUM(LINE_TOTAL) OVER(PARTITION BY SALES_ID) AS SALES_TOTAL
FROM SALES_DETAILS;
The syntax looks fine?
For this line , SUM(Line_Total) OVER() AS Total
what is the point of adding the over clause? Is this the same as doing the sum(line_total) and then adding group by at the end? Is over() allowing us to skip the group clause? And is partition by essentially equivalent to group by when doing aggregate functions?
OVER() is used to define the window, as it’s blank all rows are considered. It will return the same value as grouping but with window functions we can retain the detail rows. PARTITION BY is part of the OVER clause and further defines the window, this is different from grouping, with grouping we lose the detail rows, with window functions we retain the detail. It is important to understand that grouping is evaluated before window functions, which are usually part of the SELECT clause but can be used in ORDER BY too. You can use grouping and window functions in the same query. You can find more information on logical query processing here: m.czcams.com/video/sBRfBU5jh18/video.html
Love the accent.
Which video do you look at frames? I can't find it
m.czcams.com/video/6S7z2wabJxk/video.html
you are not a breaded dev you are jesus of SQL
Thank you
Great Video, Thanks! How to we select multiple conditions on the OVER part. e.g, Order numbers got repeated every day and we had to partition over 'day' AND 'order number' to get to the order total. Is this possible?
Yes, definitely, as you mentioned in the comments you just need to partition by day, order number.
can you provide your the create query as well so e can follow you along? loving your videos
Hi, I will put this up as soon as I can. Thanks for the feedback on the videos, let me know if there is a particular area you would like to see a video on.
Thanks a lot for this tutorial. can i get the either Db backup or script? so that i can practice?
Hi Phung, unfortunately I no longer have the script for it but because of that I have made another video where you can follow along: czcams.com/video/lBcDSsgp0RU/video.html
@@BeardedDevData Thanks you very much sir. much appreciate for your contribution.
very useful video but its hard to see the queries you wrote or the results on SQL can you zoom out them I literally can not see anything
Thanks for the feedback, I will make sure I do this on new videos.
@@BeardedDevData You are very welcome
Hi, love the tutorials. Have you done anything on using windows functions within multiple join selects?
Hi, do you mean using window functions over multiple tables joined together?
@@BeardedDevData yeah, i'm using these functions for the first time and need to join multiple tables and use the right table info in the partition for different columns. Eg count and partition from table b for one column and do the same from table c for another. Just wondering if you had covered this at all. I've tried a few tests but with different results. I'm doing this in Salesforce marketing Cloud so its not a full sql db environment, so that might be why. Its sql server 2005.
By the way love your videos. I'm learning a lot!
I haven’t done a video on different partitions and joining tables in the same query but I will get one up as soon as possible.
How can we filter in these situations? I am trying to use a "WHERE" to only see Line_Count >= 3 but it's not working
Because the window function happens in SELECT or ORDER BY phases you cannot filter in the WHERE clause in the same statement. To do this you need to use a derived table, SELECT * FROM ( ) AS D WHERE Line_Total >= 3
Is there a link to the data used?
Hi, unfortunately I don't have the data but if you like to follow along, you can here: czcams.com/video/lBcDSsgp0RU/video.html
Thank you! But please use some lower screen resolution. Not everyone is able to watch this in HD quality.😢
Sure, thanks for the feedback.
What if you wanted get the Sales_Details_ID on the row with an aggregate value (say MAX(Line_Total) ) within a Sales_Id group? So I want to look at each Sales_Id group, and get the Sales_Details_Id on the row with the maximum line_total. I've been looking for a way to do this without a CTE but that might end up being the only way, CTE + RowNumber() partition by sales ID
So what I want is something like
SELECT Sales_Details_ID, MAX(Line_Total)
FROM Sales_Details
GROUP BY Sales_ID
(This wouldn't work however because Sales_Details_ID would have to be in the GROUP BY clause
Can you tell me the relationship between sale_id and sales_details_id? The reason I ask is that if one sale_id can have many sales_details_ids related then when you group by sale_id which of the sales_details_ids would need to be returned? As one value can only be returned then you need an aggregate function on sales_details_id. If it’s a one to one relationship then you can group by sales_details_id.
@@BeardedDevData So in my case it's EmployeeID, EmployeeReviewScore, and ReviewDate. The relationship is one EmployeeID to many EmployeeReviewScore and ReviewDate. What I want is the most recent EmployeeReviewScore for each employeeID. So I would need to do something like this:
SELECT EmployeeID, EmployeeReviewScore, MAX(ReviewDate)
FROM table
GROUP BY EmployeeID
I only want to group by EmployeeID, but SQL Server syntax requires you to group by ALL non-aggregate fields. If I add EmployeeReviewScore to the GROUP BY clause, then I'll get multiple rows for each EmployeeID, which I don't want.
I've solved this by creating a CTE ordered by Date with a rownumber() partition and joining to that, but I was just wondering if there is a cleaner way I can do this without a CTE.
In the scenario you have explained a CTE is the perfect choice, to get the result you require you need to perform multiple operations on the data, first identify the MAX(review date) and then use that to identify the employee review score. Of course there are other ways to do it using other types of temporary objects but I like to use CTEs for this exact situation. In fact when somebody asks me what a CTE is and what’s it’s used for I would describe this exact problem.
@@BeardedDevData Okay, thanks for the advice and informational video
Good video. Thanks. Can we use where clause on aggregate function (eg: select count(*) over(partition by department_id order by department_id)no_emp ,e.* from employees e where count(*) >3;
Unfortunately you can’t within the WHERE clause as this is evaluated before the aggregations are applied. If you need to filter based on an aggregate function you have the option of HAVING COUNT(*) > 3 if using GROUP BY or use a CTE and then adding a WHERE clause when selecting from the CTE.
Need the dataset please
I have uploaded another video that will allow you to follow along, m.czcams.com/video/lBcDSsgp0RU/video.html
@@BeardedDevData Thanks
can we get the script for the database which is being used in video for explanation?
I haven’t published the script anywhere, but I am going to make sure in my future videos that the scripts are available.
Is it anyway possible to have a copy of the database that you used for the demo
Hi, unfortunately I no longer have the database but as so many people have requested it I’m going to upload a new copy that people can follow along to. I will upload it this weekend.
Thanks Dev.... But I managed with the AdventureWorks database
you need to zoom in. Cant see your code
Hi, please see my updated video , it allows you to follow along and I zoom in, m.czcams.com/video/lBcDSsgp0RU/video.html
Best