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

One of the most confusing topics for developers coming from traditional languages like C, C++, Java, C#, and VB to SQL, is the evaluation of predicates. In most languages predicate logic is evaluated left to right. SQL is a different story. It follows the concept of all-at-once operations. This means all logical predicates will be evaluated at the same time. In practice, the SQL engine is free to process the logical expressions in any order it finds appropriate and cost effective to retrieve the data.

Below is example to illustrate this behavior. Given a table with account policies, where a policy may be represented only as numeric values for certain accounts, and a mix of alpha-numeric characters for other accounts. Knowing that account 2 has policies that can be represented only as numeric values, the goal is to retrieve policies that are greater than 50000.

An attempt to add a first predicate to filter on account 2 and then a second one to select policies greater than 50000 will fail. Even if the query is formed with a derived table to extract first all policies for account 2 and then the outer query filters on the policy, it will not work. The derived table is expanded in the main query plan and a single query plan is produced. Then the query engine is free to push up and down the predicates in the plan as it finds efficient.

CREATE TABLE AccountPolicies (
 account_nbr INT,
 policy VARCHAR(20),
 PRIMARY KEY (account_nbr, policy));
 
INSERT INTO AccountPolicies VALUES(1, 'P1000234'); 
INSERT INTO AccountPolicies VALUES(1, 'P1020256');
INSERT INTO AccountPolicies VALUES(2, '1001');
INSERT INTO AccountPolicies VALUES(2, '5002');
INSERT INTO AccountPolicies VALUES(2, '50001');
INSERT INTO AccountPolicies VALUES(2, '50005');
INSERT INTO AccountPolicies VALUES(2, 'P50005');
 
-- Second predicate may be evaluated first
-- resulting in conversion error
SELECT account_nbr, policy
FROM AccountPolicies
WHERE account_nbr = 2
  AND CAST(policy AS INT) > 50000;
 
-- The derived table is expended in the
-- query plan and predicates can be pushed
-- up or down in the plan  
SELECT account_nbr, policy
FROM (SELECT account_nbr, policy
      FROM AccountPolicies
      WHERE account_nbr = 2) AS P
WHERE CAST(policy AS INT) > 50000;

One way to solve this is to use a CASE expression to force the desired evaluation because WHEN clauses in CASE are evaluated in order. This solution not only demonstrates how to handle the evaluation process, but adds verification that only policies with numeric values are casted (as usual business rules change and later account 2 may be allowed to create policies with alpha-numeric characters).

-- Enforce sequence of evaluating conditions
-- and cast only valid values  
SELECT account_nbr, policy
FROM AccountPolicies
WHERE CASE 
        WHEN account_nbr = 2
        THEN CASE 
               WHEN policy NOT LIKE '%[^0-9]%'
               THEN CASE 
                      WHEN CAST(policy AS INT) > 50000
                      THEN 'True'
                      ELSE 'False'
                    END
               ELSE 'False'
             END             
        ELSE 'False'
      END = 'True';