Select column based on sum of another column

Question

Let's say I have

SalesManagerId, SaleAmount, ProductId

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

Is this possible in one query?

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 a 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 a 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