CODEWITHSUNDEEP
sql-serversummaxaggregatemin
Louis
Louis

Reputation: 21

SQL Server : getting the max and min of a sum aggregate

I have a list of orders for products with a ProductID and the quantity ordered.

For example:

ProductID Quantity
------------------
 1         5
 2         2
 3         5
 1         2
 3         4
 2         8

How do I get only the ProductID of the product which sold the most and least in SQL SERVER. I tried: 

SELECT ProductID, SUM(Quantity) AS Total
FROM [Order Details]
GROUP BY ProductID
ORDER BY Total DESC

Now need only the max and min of Total.

Upvotes: 0

Views: 1716

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

Use a common table expression or a derived table to get the sum, and then query for min and max:

WITH CTE AS
(
    SELECT ProductID, sum(Quantity) as Total
    FROM [Order Details]
    GROUP BY ProductID
)

SELECT MIN(Total) As Lowest, MAX(Total) AS highest
FROM CTE

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

We can try using ROW_NUMBER here, twice:

WITH cte AS (
    SELECT ProductID, SUM(Quantity) AS Total,
        ROW_NUMBER() OVER (ORDER BY SUM(Quantity)) rn_least,
        ROW_NUMBER() OVER (ORDER BY SUM(Quantity) DESC) rn_greatest
    FROM [Order Details]
    GROUP BY ProductID
)

SELECT
    ProductID,
    CASE WHEN rn_least = 1
         THEN 'least' ELSE 'GREATEST' END AS label,
    Total
FROM cte
WHERE
    rn_least = 1 OR rn_greatest = 1
ORDER BY
    Total;

Upvotes: 0

Related Questions