SQL: Select Top 3 Records + Sum of Quantity

Question

I would like to display the top 3 records from the existing Orders table. In order to accomplish this, I need to calculate the sum of each product's quantity.

Existing Records:

OrderNo     ProductID     Quantity
1           1             50
1           2             30
1           3             20
2           2             30
3           1             100
3           4             50
4           1             20
4           5             10
5           2             10

Expected Output

ProductID     Quantity
1             170
2             70
4             50

Solution 1

You need to SUM and then ORDER BY this summary value:

SELECT TOP 3 ProductID, SUM(Quantity) as qSum
FROM Table
GROUP BY ProductID
ORDER BY qSum DESC

Solution 2

SELECT TOP 3 ProductID, SUM(Quantity) as SUMQUANTITY
FROM Table1
GROUP BY ProductID
ORDER BY SUMQUANTITY desc

SQL Fiddle Demo

Comments

[Amit Singh]

how and why the expected output come from current table?

[Prahalad Gaggar]

I think for ProductID = 1, Quantity should be 170 :)

[abramlimpin]

Got the same answer except for the ORDER BY qSum. Thank you.