SQL Tutorial - Window Functions - Calculate Running Totals, Averages

Another fantastic SQL Tutorial brought to you by BeardedDev.

If you are new to working with Window Functions check out this video:
https://youtu.be/H6OTMoXjNiM

T-SQL Querying
https://www.amazon.com/gp/product/0735685045/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=0735685045&linkCode=as2&tag=beardeddev05-20&linkId=e93b0f7483b1fa06aca5a3aec4f7764b

T-SQL Fundamentals
https://www.amazon.com/gp/product/150930200X/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=150930200X&linkCode=as2&tag=beardeddev05-20&linkId=70aa2ffa3e84cab66cb964ca1e9cee62

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
https://www.amazon.com/gp/product/0735658366/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=0735658366&linkCode=as2&tag=beardeddev05-20&linkId=b88746aea666c9f98da147e70b4491ca

In this video we learn how to use Window Functions to calculate running totals and running averages. This video teaches about Window Frames:
Rows
Range

Preceding
Current Row
Following

Window Frames are a filtered portion of a partition.

Window Functions were first introduced in SQL Server 2005 but further enhancements and support was added in SQL Server 2012.

Window Functions can only be included within SELECT or ORDER BY clauses.

Functions Available:
Aggregate - COUNT, SUM, MIN, MAX, AVG
Ranking - ROW_NUMBER, RANK, DENSE_RANK, NTILE
Offset - FIRST_VALUE, LAST_VALUE, LEAD, LAG
Statistical - PERCENT_RANK, CUME_DIST, PERCENTILE_CONT, PERCENTILE_DIST

Windows Functions also have FRAMES
ROWS
RANGE

Window Functions are a powerful tool within SQL Server and I am excited to bring more videos and tutorials working with Window Functions in the future.

SQL:
SELECT
Sales_Id
, Sales_Date
, Sales_Total
, SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Running Total]
FROM dbo.Sales_2
WHERE Sales_Cust_Id = 3
ORDER BY Sales_Date

SELECT
Sales_Id
, Sales_Date
, Sales_Total
, SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS [Running Total]
FROM dbo.Sales_2
WHERE Sales_Cust_Id = 3
ORDER BY Sales_Date

SELECT
Sales_Id
, Sales_Date
, Sales_Total
, SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS [Running Total]
FROM dbo.Sales_2
WHERE Sales_Cust_Id = 3
ORDER BY Sales_Date

SELECT
Sales_Id
, Sales_Date
, Sales_Total
, SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS UNBOUNDED PRECEDING) AS [Running Total]
FROM dbo.Sales_2
WHERE Sales_Cust_Id = 3
ORDER BY Sales_Date

SELECT
Sales_Id
, Sales_Date
, Sales_Total
, SUM(Sales_Total) OVER(ORDER BY Sales_Date ROWS UNBOUNDED PRECEDING) AS [Running Total]
, CAST(AVG(Sales_Total) OVER(ORDER BY Sales_Date ROWS UNBOUNDED PRECEDING) AS DECIMAL(8, 2)) AS [Running Average]
FROM dbo.Sales_2
WHERE Sales_Cust_Id = 3
ORDER BY Sales_Date

Видео SQL Tutorial - Window Functions - Calculate Running Totals, Averages канала BeardedDev