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

Question

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

Thanks in advance

Use a having clause for that condition. And remember to include a group by too. — Gordon Linoff
@GordonLinoff Do you even know what i want to do afterwards with the information? — Tomato

Lukasz Szozda Lukasz Szozda · Accepted Answer · 2018-05-06T19:17:09

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;

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

1 Answers