Sql: Select Top 3 Records + Sum of Quantity

SQL: Select Top 3 Records + Sum of Quantity

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

SQL - select top 10 rows with the highest number (counted)

You need to SUM number field and order by it like this;

 SELECT TOP 10 
      playerID, 
      SUM([number]) as goals,
      [league],
      [yearID],
      SUM([YellowCard]) as YellowCards,
      SUM([RedCard]) as RedCards,
  FROM [ms4033].[dbo].[Shooter]
  WHERE yearID=28 AND league=4
  GROUP BY playerID, league, yearID
  ORDER BY goals DESC

How to SELECT top N rows that sum to a certain amount?

One approach:

select t1.amount 
from MyTable t1
left join MyTable t2 on t1.amount > t2.amount
group by t1.amount
having coalesce(sum(t2.amount),0) < 7

SQLFiddle here.

---

Related Topics