I’m trying to calculate in a subquery the sum of a column that’s been joined in a one-to-many relationship, grouped by a foreign key so that for every record relating to the same key there’s a subtotal of all of those records.

The subquery I tried was

(SELECT SUM(est_valuation.total_value) 
FROM est_valuation
WHERE est_valuation.current_val = 'Y'
GROUP BY est_valuation.site_id)
AS 'Combined Valuation(s) Total'

It works without the GROUP BY clause but that’s no use, and it can’t be calculated in the main query because I only want the WHERE est_valuation.current_val = ‘Y’ line specifically for this calculation and don’t want to filter by current valuation status for the rest of the query.

My attempt to put this through came back with error

SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more
than 1 row

and I’m all out of ideas