Get top column base on maximum other column using group by?

Question

How to get top column base on maximum other column using group by?

My raw data:

DECLARE @TB TABLE (ID INT, APP VARCHAR(25), PRICE MONEY)
INSERT INTO @TB
VALUES
(1, 'Apple', 10),
(1, 'Banana', 30),
(1, 'Orange', 20),
(2, 'Apple', 20),
(2, 'Banana', 30),
(2, 'Orange', 40)

This what I want:

Explain:

TOP_APP = Banana because MAX(PRICE) GROUP BY ID,

TOTAL = 60 because SUM(PRICE) GROUP BY ID.

Answer 1

As my assumption you want the result is

SELECT identity (int,1,1) as ID,
    APP AS TOP_APP
    ,SUM(PRICE) AS TOTAL INTO #T
FROM @TB
GROUP BY APP
order by SUM(PRICE)

select * from #t

Answer 2

Demo on db<>fiddle

;WITH cte_TempTable as 
(
  SELECT Id, app, price,
                SUM(Price) OVER(PARTITION BY ID) Total,
                ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Price DESC) Row_Number
   FROM @TB
)

SELECT Id, app as TOP_APP, Total 
FROM cte_TempTable
WHERE Row_Number = 1

Output

Id  TOP_APP Total
1   Banana  60.0000
2   Orange  90.0000

Answer 3

You can use ROW_NUMBER and aggregation to achieve your required output-

DEMO HERE

SELECT A.ID,A.App,A.SUM 
FROM
(
    select *,
    SUM(Price) OVER(PARTITION BY ID) SUM,
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Price DESC) RN
    from @tb
)A
WHERE RN = 1