Select column with maximum value in another column but with aggregate SUM calculation

Question

For each name, I need to output the category with the MAX net revenue and I am not sure how to do this. I have tried a bunch of different approaches, but it basically looks like this:

SELECT Name, Category, MAX(CatNetRev)
FROM (
SELECT Name, Category, SUM(Price*(Shipped-Returned)) AS "CatNetRev"
FROM a WITH (NOLOCK)
    INNER JOIN b WITH (NOLOCK) ON b.ID = a.ID
    ...
    -- (bunch of additional joins here, not relevant to question)
WHERE ... -- (not relevant to question)
GROUP BY Name, Category
) a GROUP BY Name;

This currently doesn't work because "Category" is not contained in an aggregate function or Group By (and this is obvious) but other approaches I have tried have failed for different reasons.

Each Name can have a bunch of different Categories, and Names can have the same Categories but the overlap is irrelevant to the question. I need to output just each unique Name that I have (we can assume they are already all unique) along with the "Top Selling Category" based on that Net Revenue calculation.

So for example if I have:

Name:	Category:	"CatNetRev":
A	1	100
A	2	300
A	3	50
B	1	300
B	2	500
C	1	40
C	2	20
C	3	10

I would want to output:

Name:	Category:
A	2
B	2
C	1

What's the best way to go about doing this?

Your query references two tables but you have only shown data for one of them. — Gordon Linoff, May 04 '21 at 18:58
@GordonLinoff Do you mean the internal query? It has like 7 different tables that are joined for specific WHERE conditions, I didn't think it was relevant to the question. — pass341, May 04 '21 at 19:02
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, May 04 '21 at 20:20

score 1 · Answer 1 · answered May 04 '21 at 19:05

Having to guess at your data schema a bit, as you didn't alias any of your columns, or define what table a vs b really was (as Gordon alluded). I'd use CROSS APPLY to get the max value, then bind the revenues in a WHERE clause, like so.

DECLARE @Revenue TABLE
(
    Name        VARCHAR(50)
    ,Category   VARCHAR(50)
    ,NetRevenue DECIMAL(16, 9)
);

INSERT INTO @Revenue
(
    Name
    ,Category
    ,NetRevenue
)
SELECT  Name
        ,Category
        ,SUM(a.Price * (b.Shipped - b.Returned)) AS CatNetRev
FROM    Item AS a
        INNER JOIN ShipmentDetails AS b ON b.ID = a.ID
WHERE   1 = 1
GROUP BY
        Name
        ,Category;

SELECT  r.Name
        ,r.Category
FROM    @Revenue AS r
        CROSS APPLY (
                        SELECT  MAX(r2.NetRevenue) AS MaxRevenue
                        FROM    @Revenue AS r2
                        WHERE   r.Name = r2.Name
                    ) AS mr
WHERE   r.NetRevenue = mr.MaxRevenue;

Not to split hairs, but should this be OUTER APPLY instead of CROSS APPLY? CROSS APPLY is usually used without a condition. — oglester, May 04 '21 at 21:05
I've rarely ever used CROSS APPLY without a condition. That would result in an aggregate of the entire subquery, instead of one for each row of the parent. https://www.sqlshack.com/the-difference-between-cross-apply-and-outer-apply-in-sql-server/ — Mike Petri, May 04 '21 at 22:56

score 1 · Answer 2 · answered May 04 '21 at 19:12

1

you can use window functions:

select * from 
  ( 
    select * , rank() over (partition by Name order by CatNetRev desc) rn
    from table
  ) t
where t.rn = 1

answered May 04 '21 at 19:12

eshirvana

23,227
3
22
38

Select column with maximum value in another column but with aggregate SUM calculation

2 Answers2