Select a column based on the sum of another column

Question

Say I have

SalesManagerId, SaleAmount, ProductId 

I want to summarize the SaleAmount for each ( SalesManagerId , ProductId ) and grab the ProductId with the maximum value of sum(SaleAmount) .

Is this possible in a single request?

Example:

 1, 100, 1 1, 200, 1 1, 600, 1 1, 400, 2 2, 100, 3 3, 100, 4 3, 100, 4 2, 500, 6 3, 100, 5 

result:

 1, 900, 1 2, 500, 6 3, 200, 4 

Answer 1

If you have analytic functions available, you can use RANK()

Something like:

 SELECT SalesManagerId, ProductId, Total FROM ( SELECT SalesManagerId, ProductId, SUM(SaleAmount) as Total, RANK() OVER(PARTITION BY SalesManagerId ORDER BY SUM(SaleAmount) DESC) as R FROM <Table name> GROUP BY SalesManagerId, ProductId) as InnerQuery WHERE InnerQuery.R = 1 

Answer 2

Assuming at least SQL 2005 so you can use CTE :

 ;with cteTotalSales as ( select SalesManagerId, ProductId, SUM(SaleAmount) as TotalSales from YourSalesTable group by SalesManagerId, ProductId ), cteMaxSales as ( select SalesManagerId, MAX(TotalSales) as MaxSale from cteTotalSales group by SalesManagerId ) select ts.SalesManagerId, ms.MaxSale, ts.ProductId from cteMaxSales ms inner join cteTotalSales ts on ms.SalesManagerId = ts.SalesManagerId and ms.MaxSale = ts.TotalSales order by ts.SalesManagerId 

Answer 3

Use GROUP BY and ORDER:

 SELECT SalesManagerId, SUM(SaleAmount) AS SaleSum, ProductId FROM [table-name] GROUP BY SalesManagerId, ProductId ORDER BY SaleSum DESC 

Answer 4

Very good question!

Try the following:

 SELECT MAX(SUM(SaleAmount)), ProductId GROUP BY SalesManagerId, ProductId; 

Or alternatively

 SELECT SUM(SaleAmount) as Sum, ProductId GROUP BY SalesManagerId, ProductId ORDER BY Sum DESC; 

You cannot just drop the amount column and get ONLY the product identifier