CODEWITHSUNDEEP
sqlsql-servert-sql
farooq shahid
farooq shahid

Reputation: 15

How I can get the Sum from following Query?

DECLARE @VARIABLE AS BIGINT   
SET @VARIABLE = Sum((Select Top 5 SUM(Quantity) FROM Orders Group by MenuItemName Order by Quantity Desc))   
Print @VARIABLE

This Will Return a Error :

Cannot perform an aggregate function on an expression containing an aggregate or a subquery

How I can Achieve that ?

Upvotes: 0

Views: 34

Answers (2)

SqlZim
SqlZim

Reputation: 38023

You can use select @variable =, and you should probably be ordering by sum(Quantity) desc.

declare @variable as bigint;

select @variable = Sum(SumQuantity) 
  from (
    select top 5 
        SumQuantity=Sum(Quantity)
      from Orders
      group by MenuItemName
      order by Sum(Quantity) desc
   ) as x;

print @variable;

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93724

Considering you want to order the records in sum(Quantity) not just Quantity

Try this way

DECLARE @VARIABLE AS BIGINT

SET @VARIABLE = (SELECT Sum(Quantity)
                 FROM   (SELECT TOP 5 sum(Quantity) Quantity 
                         FROM   Orders
                         GROUP  BY MenuItemName
                         ORDER  BY Quantity DESC) a)

PRINT @VARIABLE

Upvotes: 3

Related Questions