Aggregate Window Functions

The addition of the OVER clause in SQL Server was a great enhancement to the T-SQL language. Using the ranking functions has helped solve an array of problems in a very efficient manner. While there is a huge benefit of the ranking functions, it is often overlooked that the OVER clause supports now aggregate window functions. This means that the window aggregate function (SUM, AVG, COUNT, MIN, MAX) computes a value for each row from a result set derived from the window (partition). That opens an opportunity to solve problems using new methods.

Here are a couple examples to demonstrate using aggregate window functions with the OVER clause.

CREATE TABLE Loans (
  loan_nbr INT NOT NULL PRIMARY KEY,
  loan_date DATETIME NOT NULL,
  loan_amount DECIMAL(12, 2) NOT NULL
              DEFAULT 0.0,
  customer_nbr INT NOT NULL,
  loan_type CHAR(1) NOT NULL
            DEFAULT 'P'
            CHECK (loan_type IN ('P',  -- personal
                                 'B')) -- business
  );
 
INSERT INTO Loans 
VALUES (1, '20080801', 12000.00, 2, 'P'),
       (2, '20080805', 15700.00, 1, 'B'),
       (3, '20080610', 12000.00, 3, 'P'),
       (4, '20080401', 5000.00, 1, 'P'),
       (5, '20080715', 25000.00, 4, 'B'),
       (6, '20080610', 25000.00, 5, 'P'),
       (7, '20080501', 1000.00, 6, 'P'),
       (8, '20080810', 6000.00, 7, 'B'),
       (9, '20080815', 2000.00, 8, 'B'),
       (10, '20080815', 1000.00, 9, 'P'),
       (11, '20080715', 5500.00, 10, 'P'),
       (12, '20080615', 1000.00, 11, 'B'),
       (13, '20080820', 6000.00, 12, 'P'),
       (14, '20080510', 28000.00, 6, 'B'),
       (15, '20080815', 2000.00, 10, 'P'),
       (16, '20080810', 1500.00, 8, 'P'),
       (17, '20080817', 10000.00, 10, 'B'),
       (18, '20080816', 2500.00, 9, 'P');   
 
-- Aggregate window functions with the OVER clause
SELECT loan_nbr, loan_type, loan_amount, customer_nbr,
       SUM(loan_amount) OVER(PARTITION BY customer_nbr) AS total_customer_loans,
       AVG(loan_amount) OVER(PARTITION BY customer_nbr) AS avg_customer_loan_amt,
       MAX(loan_amount) OVER(PARTITION BY customer_nbr) AS max_customer_loan_amt,
       MIN(loan_amount) OVER(PARTITION BY customer_nbr) AS min_customer_loan_amt,
       COUNT(*) OVER(PARTITION BY customer_nbr) AS count_customer_loans
FROM Loans;
 
-- Calculate percent for current loan based on total customer loans
-- and total of all loans
SELECT loan_nbr, loan_type, loan_amount, customer_nbr,
       loan_amount /
       SUM(loan_amount) OVER(PARTITION BY customer_nbr) AS percent_of_customer_loans,
       loan_amount /
       SUM(loan_amount) OVER() AS percent_of_all_loans
FROM Loans;
 
-- Get customers (and all their loans) with more than 2 loans
SELECT customer_nbr, loan_nbr, loan_amount, cnt
FROM (SELECT customer_nbr, loan_nbr, loan_amount,
             COUNT(*) OVER(PARTITION BY customer_nbr) AS cnt
      FROM Loans) AS L
WHERE cnt >= 2;

Resources:

OVER Clause
http://msdn.microsoft.com/en-us/library/ms189461.aspx