SQL Workshop - Selecting columns without a non-aggregate column

That’s happening because column m.unit is used in the SELECT clause but not in the GROUP BY clause.

This is a perfectly valid and reasonable response from our DBMS, but in this exceptional case, selecting columns without including a non-aggregate column in the group by clause would be handy.

But this can’t happen if we are not explicit in what we ask for :

SELECT
a.account_category,
a.account_id,
sum (b.value) as summed_amount
FROM (
SELECT m.material_id, m.year
CASE
WHEN m.unit = 'PIECES'
THEN m.mean_value * r.quantity * m.ratio
ELSE m.mean_value * r.quantity / m.ratio
END as value
from materials m, requests r
where r.year = '1/1/13' and
r.year = m.year and
r.material_id = m.material_id) b,
accounts a
WHERE
a.material_id = b.material_id and
a.year = b.year
GROUP BY account_category, account_id

This works because of an inline view (marked in red) which acts as a temporary table holding the value for each row :