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 :
Result Set 3
and exporting material_id and year to the outside scope, both of which will subsequently used for joining with the Accounts table of the outer query ( a.material_id = b.material_id and a.year = b.year), producing :
Result Set 4
and finally grouped into :
Result Set 5
enabling us to answer questions like “how much money on average, did the consumption of fruit cost us this year?”, so that we can estimate how much money we should reserve for next year’s purchases.
With open source you can never write that a project is dead, but in this case it can't really get any closer to dead. Cyanogen was the only credible alternative to a Google and to be honest it was mor [ ... ]
The latest big surprise hit Amazon's Alexa. Voice control seems to be the next big thing, but Amazon makes it hard to extend its reach to really new skills. An alternative is Mycroft, an open source A [ ... ]