SQL Tutorial - Window Functions (Follow Along)

Another video brought to you by BeardedDev, bringing you tutorials on Data Engineering, Business Intelligence, T-SQL Programming and Data Analysis.
If you like the video you can support me on Patreon, / beardeddev
A lot of views have requested the ability to follow along with my tutorials particularly on a tutorial I have already on Window Functions, this video builds on the previous one by adding the ability to follow along with the tutorials with the code examples below.
The original video can be found here: • SQL Tutorial - Window ...
In this video we create a sample table and then use that data to cover different scenarios, first we take a long at using GROUP BY in SQL Server and understand the limitations. We then introduce a CTE to the query to allow us to join back to the original table to retrieve further columns that are not included in the GROUP BY. We discuss the problems with this approach, mainly that we have to join to the table multiple times, the query is also more verbose, the other downside is that we will need to create multiple CTEs or Derived Tables for each different aggregation we want to perform.
We then introduce Window Functions including how to use OVER and what PARTITION BY means, we also discuss how we can use different aggregate functions and that Window Functions are only allowed in SELECT or ORDER BY. We demonstrate different Window Functions using different PARTITION BY clause and even show a Window Function to calculate the grand total.
Lastly we demonstrate how we can use Window Functions in expressions to calculate percent of totals.
If you would like to follow along with this SQL tutorial then you can use the below SQL to create the necessary objects.
Please feel free to post any comments.
DROP TABLE IF EXISTS dbo.SalesDetails;
GO
CREATE TABLE dbo.SalesDetails
(
SalesDetailsId INT NOT NULL IDENTITY(1, 1)
CONSTRAINT PK_SalesDetails_SalesDetailsId PRIMARY KEY (SalesDetailsId),
SalesId INT NOT NULL,
SalesDate DATE NOT NULL,
ProductId INT NOT NULL,
Price MONEY NOT NULL,
Quantity INT NOT NULL,
LineTotal AS Price * Quantity
);
INSERT INTO dbo.SalesDetails (SalesId, SalesDate, ProductId, Price, Quantity)
VALUES
(1, '20200105', 6, 5.99, 2),
(1, '20200105', 5, 4.50, 1),
(1, '20200105', 4, 17.99, 4),
(2, '20200107', 2, 2.99, 2),
(2, '20200107', 3, 11.40, 1),
(3, '20200107', 6, 5.99, 4),
(3, '20200107', 2, 2.99, 2),
(3, '20200107', 3, 11.40, 1),
(3, '20200107', 9, 6.29, 4),
(4, '20200108', 9, 6.29, 2),
(4, '20200108', 8, 23.10, 1),
(4, '20200108', 1, 13.25, 4),
(4, '20200108', 2, 2.99, 2),
(4, '20200108', 3, 11.40, 1),
(5, '20200110', 4, 17.99, 4),
(6, '20200110', 7, 19.00, 2),
(6, '20200110', 9, 6.29, 10);
WITH CTE AS
(
SELECT
SalesId,
SUM(LineTotal) AS SalesTotal
FROM dbo.SalesDetails
GROUP BY
SalesId
)
SELECT
SalesDetailsId,
A.SalesId,
SalesDate,
ProductId,
Price,
Quantity,
LineTotal,
SalesTotal
FROM dbo.SalesDetails AS A
INNER JOIN CTE AS B
ON A.SalesId = B.SalesId;
SELECT
SalesDetailsId,
SalesId,
SalesDate,
ProductId,
Price,
Quantity,
LineTotal,
SUM(LineTotal) OVER(PARTITION BY SalesId) AS SalesTotal,
COUNT(SalesDetailsId) OVER(PARTITION BY SalesId) AS SalesCount,
SUM(LineTotal) OVER(PARTITION BY SalesDate) AS DailyTotal,
SUM(LineTotal) OVER(PARTITION BY SalesDate, ProductId) AS DailyProductSales,
SUM(LineTotal) OVER() AS SalesGrandTotal,
100 * SUM(LineTotal) OVER(PARTITION BY SalesId) / SUM(LineTotal) OVER() AS pcttotal
FROM dbo.SalesDetails
ORDER BY SalesId

[@chrisosomo2856]

I really appreciate the amount of effort you put into this tutorial. Your hard work is not lost on me. Your explanation are clear and examples are easy to follow along with. Thanks again and God bless🙏🏽🙏🏽

[@BeardedDevData]

Thanks so much.

[@houstonvanhoy7767]

I'm closing in on your final two videos about SQL Server window functions, and I can see how they are building in sequence. It's sinking in. Thank you!
Just when I was thinking, "I wish that Brad would temporarily comment out some of the lines and re-run the code", that was the next thing that you did. You get it, Maestro.

[@ashkrv123]

This is by far the most explanatory video on this topic...tons of thanks to you Sir.. you really are master of this domain.

[@BeardedDevData]

Thanks so much

[@MAAMECOBBINAH]

Thank you for such a thorough explanation

[@BeardedDevData]

Thank you for watching

[@gauravky5985]

@BeardedDev, I have to tell you that your way of teaching stuff in an easy manner is awesome. So, inspired by you that i've started growing a beard.

[@BeardedDevData]

Thanks so much.

[@dareemmanuel6079]

Clearly and calmly well explained. Great teacher! Let me go and check out your other sql tutorials

[@BeardedDevData]

Thanks so much.

[@lonestarstatechris]

This is the first explanation of this topic that has made sense to me. Thank you

[@abdellahaghlalou9978]

just woooow for this amazing explanation

[@vinceguitarmusic]

This is by far the best explanation I have ever seen. Thanks so much.

[@BeardedDevData]

Thanks so much.

[@Pragmatyk91]

Well done! Great examples of window functions, everything explained in very simple way :)

[@ameobiamicrobiological2366]

I use these a lot since I work with a lot of Entity/Attribute/Value tables.

[@johnbreslin4521]

That was a brilliant tutorial, it explained these functions really well. Thanks very much.

[@BeardedDevData]

Thanks so much.

[@StefanoVerugi]

extremely helpful, clear explanation, thanks

[@danielheydt9864]

Thanks for another great video!

[@KarinS-tk3qt]

Thank you!

[@BeardedDevData]

No problem.

[@dw1866]

Really good explanation! Thanks!

[@TheStefannel]

Very nice explanation!

[@Alex-hn3tx]

Fantastic explanation, thanks a lot.

[@abiydelelew5771]

Thank You Sir! Super helpful!

[@neetubajwa01]

Great explanation! Thanks for this video!

[@nataniagomourastreppel668]

This was great!

[@rohanhazra7622]

Great video!

[@cashputra]

Thank you! Very useful 10/10

[@froststorm77]

Great explanation, but there few more in OVER() functions, it would be great to watch about them.

[@MuhammadAhmed-ub7iz]

is there any video on running total with previous balance like vendor statement or bank statment

[@BeardedDevData]

Hi, check out this video: m.czcams.com/video/6S7z2wabJxk/video.html

[@marcelmarceli8246]

Thanks! Awesome tutorial & explanation.