T-SQL – Aggregate data without joining to a subquery

When creating a reports, you may need to aggregate only a portion of the data in the main report. In the past, I would have created a sub query to get the aggregate data and the join it to the main report. However, using the new OVER function you can aggregate data without joining to a subquery.

It is common to use aggregate functions with the GROUP function like COUNT and SUM.


SELECT CustomerId, InvoiceId,
COUNT(*) OVER(PARTITION BY CustomerId) as NumberOfInvoices
FROM Invoices

The result of this query, is that you can return the number of invoices per customer, while still showing each invoice id.
The result of this query will the customer sales per year

CustomerId

InvoiceId

NumberOfInvoices

Cust1

123

2

Cust1

124

2

Cust2

125

3

Cust2

126

3

Cust2

127

3

Using the OVER with PARTITION can be used in the ORDER BY clause as well. This will enable you to sort the record set by the grouping.


SELECT CustomerId, InvoiceId,
COUNT(*) OVER(PARTITION BY CustomerId) as NumberOfInvoices
FROM Invoices
ORDER BY COUNT(*) OVER(PARTITION BY CustomerId) DESC;

The result of this query, is that you can return the number of invoices per customer, while still showing each invoice id.

CustomerId

InvoiceId

NumberOfInvoices

Cust2

125

3

Cust2

126

3

Cust2

127

3

Cust1

123

2

Cust1

124

2

If you want to use the new RANK, DENSE_RANK, ROW_NUMBER or NTILE ranking functions, you will need to use them with the OVER clause.