SQL与Group By的最大值的总和

Question

我有一个表，结构如下:

100 7580

我们可以尝试使用ROW_NUMBER窗口函数和子查询，通过称为rn的原始行获得最大流行度。

然后使用条件聚合函数对所有最大流行度求和

select Fruit, 
Color, 
SUM(IIF(rn = 1,Popularity,0)) Popularity, 
SUM(Price) Price
FROM (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY Fruit, Color,origin ORDER BY Popularity DESC) rn
FROM FRUITS_TABLE
) t1
Group by Fruit, Color

sqlfiddle

CREATE TABLE Fruit (
Fruit varchar(8) NOT NULL,
Color varchar(8) NOT NULL,
Origin varchar(2)    NULL,
Popularity int NOT NULL,
Price      int NOT NULL,
-- What is the PK?
)
INSERT INTO Fruit (Fruit, Color, Origin, Popularity, Price)
VALUES 
('Apple', 'Red', 'IN', 100, 100),
('Apple', 'Red', 'IN', 90, 50),
('Apple', 'Red', 'FR', 50, 75),
('Apple', 'Red', 'FR', 50, 80),
('Apple', 'Red', NULL, 20, 20)
SELECT Fruit, Color, SUM(PriceSum) AS PriceSum, SUM(PopularityMax) AS PopularityMaxSum
FROM (
SELECT Fruit, Color, Origin,
SUM(Price) AS PriceSum,
MAX(Popularity) AS PopularityMax
FROM Fruit
GROUP BY Fruit, Color, Origin
) T
GROUP BY Fruit, Color