fraggle fraggle - 2 years ago 102
SQL Question

How do I sum over multiple criteria in T-SQL?

I'm trying to improve on my very basic SQL querying skills and am using the AdventureWorks2012 sample database in SQL Server 2012. I have used

like this:

SUM(SubTotal) OVER (PARTITION BY CustomerID), CustomerID

To get the total sales value for each customer, however I'd like to sum the
by customer & year using
to extract just the year portion of the order date.

Firstly it appears that I can't use the year portion of the order date to sum by year independently of customer so this approach isn't going to work anyhow.

Secondly I can't see any way to use multiple partition criteria.

I suspect that my inexperience is leading me to think about this in the wrong way so a theoretical approach would be as useful as a specific solution.

I guess I'm looking for something that is functionally similar to Excel's

Answer Source

First, the correct way to write your query is:

SELECT CustomerID, SUM(SubTotal)
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

Using SELECT DISTINCT with window functions is clever. But, it overcomplicates the query, can have poorer performance, and is confusing to anyone reading it.

To get the information by year (for each customer), just add that to the SELECT and GROUP BY:

SELECT CustomerID, YEAR(OrderDate) as yyyy, SUM(SubTotal)
FROM Sales.SalesOrderHeader
GROUP BY CustomerID, YEAR(OrderDate)
ORDER BY CustomerId, yyyy;

If you actually want to get separate rows with subtotals, then study up on GROUPING SETS and ROLLUP. These are options to the GROUP BY.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download