I have the following query which fails with error - An aggregate may not appear in the set list of an UPDATE statement. Why can't I update a table with aggregated data from another table?
SET monthCount = SUM(finalQty),
monthValue = SUM(finalValue),
modifiedDate = GETDATE()
FROM salesData sd
INNER JOIN customer c ON c.efssNumber = sd.efssCustomerNumber AND c.number = sd.customerNumber
INNER JOIN product p ON p.productNumber = sd.productNumber
INNER JOIN salesDataAggregated sda ON sda.customerRef = c.id AND sda.productRef = p.id AND sda.[month] = MONTH(sd.orderDate)