CODEWITHSUNDEEP
sqlsql-servert-sql
Tomato
Tomato

Reputation: 153

An aggregate may not appear in the WHERE clause (MSSM studio)

I am trying to create a trigger for insert on Advertisement table. When trying to write this cursor

declare Users cursor for 
    Select "User".IDUser, Sum(Price)   
    from "User"
    inner join Purchase as pu on "User".IDUser = pu.IDUser  
    inner join PurchaseProduct as pp on pu.IDPurchase = pp.IDPurchase
    inner join Product as pr on pp.IDProduct = pr.IDProduct
    inner join inserted on pr.IDProduct = inserted.IDProduct
    where pr.ProductType = (select ProductType 
                            from Product
                            inner join Advertisement on Product.IDProduct = Advertisement.IDProduct
                            inner join inserted on Advertisement.IDProduct = inserted.IDProduct
                            where Advertisement.IDAdvertisement = inserted.IDAdvertisement)
      and Sum(Price) > 50;

I get this error

Msg 147, Level 15, State 1, Procedure AutomaticUserAdvertisementScoreCalculating, Line 15 [Batch Start Line 113]
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

What might be the error here? Below you can see my DB structure

ER of DB

Thanks in advance

Upvotes: 2

Views: 119

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175944

You could use HAVING to filter rows after aggregation:

declare Users cursor for 
Select "User".IDUser, Sum(Price) from "User"
join Purchase as pu on "User".IDUser = pu.IDUser  
join PurchaseProduct as pp on pu.IDPurchase = pp.IDPurchase
join Product as pr on pp.IDProduct = pr.IDProduct
join inserted on pr.IDProduct = inserted.IDProduct
where pr.ProductType = (select ProductType from Product
              join Advertisement on Product.IDProduct = Advertisement.IDProduct
              join inserted on Advertisement.IDProduct = inserted.IDProduct
              where Advertisement.IDAdvertisement = inserted.IDAdvertisement)
GROUP BY "User".IDUser
HAVING Sum(Price) > 50;

Upvotes: 2

Related Questions