SQL Workshop - Selecting columns without including a non-aggregate column in the group by clause

### New Book Reviews!

 SQL Workshop - Selecting columns without including a non-aggregate column in the group by clause
Written by Nikos Vaggalis
Article Index
SQL Workshop - Selecting columns without including a non-aggregate column in the group by clause
The Solution

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 aWHERE    a.material_id = b.material_id and    a.year = b.yearGROUP 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 Material_id Year Value TX002 1/1/2013 $360 TX003 1/1/2013$680.01

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 Account_category Account_id Value 01 220201511 $360 01 220201511$680.01

and finally grouped into :

 Result Set 5 Account_category Account_id Summed_amount 01 220201511 \$1040.01

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.

#### Other SQL Workshops

SQL Workshop - Selecting columns without including a non-aggregate column in the group by clause

SQL Workshop - Subselects And Join

More SQL Workshop soon!

 //No Comment - Approximate Edit Distance, Irrational Guards & DCT In 14 Additions07/02/2017• On Practical Accuracy of Edit Distance Approximation Algorithms • Irrational Guards are Sometimes Needed • DCT-like Transform for Image Compression Requires 14 Additions Only + Full Story Komodo 10.2 Adds Slack Sharing23/02/2017An updated version of ActiveState's cross-platform multi-language programming IDE, Komodo, has been released with the better unit testing and Slack sharing among its improvements and enhancements. &nb [ ... ] + Full Story More News

Last Updated ( Thursday, 19 December 2013 )