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
– Gordon Linoff, Commented May 4, 2021 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
– pass341, Commented May 4, 2021 at 19:02

eshirvana · Accepted Answer · 2021-05-04 19:12:20Z

2

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 4, 2021 at 19:12

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike Petri · Accepted Answer · 2021-05-04 19:05:36Z

1

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;

answered May 4, 2021 at 19:05

Mike Petri

6054 silver badges12 bronze badges

3 Comments

Mike Petri Over a year ago

Please mark as answer if you found it helpful :-)

oglester Over a year ago

Not to split hairs, but should this be OUTER APPLY instead of CROSS APPLY? CROSS APPLY is usually used without a condition.

Mike Petri Over a year ago

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. sqlshack.com/…

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related