Increasing window complexity

Intermediate SQL Server

Ginger Grant

Instructor

Reviewing aggregations

SELECT SalesPerson, SalesYear, CurrentQuota, 
       SUM(CurrentQuota)  
       OVER (PARTITION BY SalesYear) AS YearlyTotal, 
       ModifiedDate as ModDate
FROM SaleGoal

+------------+----------+------------+------------+----------+
|SalesPerson |SalesYear |CurrentQuota|YearlyTotal | ModDate  |
+------------+----------+------------+------------+----------+
|Bob         |2011      |28000.00    |1551000.00  |2011-04-16|
|Bob         |2011      |7000.00     |1551000.00  |2011-07-17| 
|Bob         |2011      |91000.00    |1551000.00  |2011-10-17|
|Mary        |2011      |140000.00   |1551000.00  |2012-04-15|
|Mary        |2011      |70000.00    |1551000.00  |2012-07-15|
|Mary        |2011      |154000.00   |1551000.00  |2012-01-15|
|Mary        |2012      |107000.00   |1859000.00  |2012-01-16|
...
+------------+----------+------------+------------+----------+

Adding ORDER BY to an aggregation

SELECT SalesPerson, SalesYear, CurrentQuota, 
       SUM(CurrentQuota)  
       OVER (PARTITION BY SalesYear ORDER BY SalesPerson) AS YearlyTotal, 
       ModifiedDate as ModDate
FROM SaleGoal

+------------+----------+------------+------------+----------+
|SalesPerson |SalesYear |CurrentQuota|YearTotal   | ModDate  |
+------------+----------+------------+------------+----------+
|Bob         |2011      |28000.00    |35000.00    |2011-04-16|
|Bob         |2011      |7000.00     |35000.00    |2011-07-17| 
|Mary        |2011      |367000.00   |958000.00   |2011-10-17|
|Mary        |2011      |556000.00   |958000.00   |2012-04-15|
|Bob         |2012      |70000.00    |401000.00   |2012-07-15|
|Bob         |2012      |154000.00   |401000.00   |2012-10-16|
...
+------------+----------+------------+------------+----------+

Creating a running total with ORDER BY

SELECT SalesPerson, SalesYear, CurrentQuota, 
       SUM(CurrentQuota) 
       OVER (PARTITION BY SalesYear ORDER BY ModifiedDate) as RunningTotal, 
       ModifiedDate as ModDate
FROM SaleGoal

+------------+----------+------------+------------+----------+
|SalesPerson |SalesYear |CurrentQuota|RunningTotal| ModDate  |
+------------+----------+------------+------------+----------+
|Bob         |2011      |28000.00    |28000.00    |2011-04-16|
|Mary        |2011      |367000.00   |395000.00   |2011-07-17| 
|Mary        |2011      |556000.00   |951000.00   |2011-10-17|
|Bob         |2011      |7000.00     |958000.00   |2012-04-15|
|Bob         |2012      |70000.00    |70000.00    |2012-01-15|
|Mary        |2012      |502000.00   |572000.00   |2012-01-16|
...
+------------+----------+------------+------------+----------+

Adding row numbers

ROW_NUMBER() sequentially numbers the rows in the window
ORDER BY is required when using ROW_NUMBER()

Adding row numbers in T-SQL

SELECT SalesPerson, SalesYear, CurrentQuota,  
       ROW_NUMBER() 
       OVER (PARTITION BY SalesPerson ORDER BY SalesYear) AS QuotabySalesPerson
FROM SaleGoal

+------------+----------+------------+------------------+
|SalesPerson |SalesYear |CurrentQuota|QuotabySalesPerson|
+------------+----------+------------+------------------+
|Bob         |2011      |28000.00    |1                 |
|Bob         |2011      |7000.00     |2                 | 
|Bob         |2011      |70000.00    |3                 |
|Bob         |2011      |154000.00   |4                 |
|Bob         |2012      |70000.00    |5                 |
|Bob         |2012      |107000.00   |6                 |
|Bob         |2012      |91000.00    |7                 |
|Mary        |2011      |367000.00   |1                 |
...
+------------+----------+------------+------------------+

Let's practice!

Intermediate SQL Server