I have a table having the following structure.

I would like to get the total price (100 50 75 80 20) grouped by Fruit and Color, including sum of maximum popularity by origin (100 50 20). Below is the expected result.

I have tried group by with distinct sum but that giving wrong result.

select Fruit, Color, SUM(distinct Popularity), SUM(Price) FROM FRUITS_TABLE Group by Fruit, Color

Please share if any solution to achieve this result in a single query.

I am using MS SQL.

Thank You

CodePudding user response：

We can try to use the ROW_NUMBER window function with a subquery to get maximum popularity by the origin row called rn.

Then use the condition aggregate function to sum all maximum popularity

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

CodePudding user response：

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