|SQL Workshop - Selecting columns without a non-aggregate column|
|Written by Nikos Vaggalis|
Page 2 of 2
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 :
This works because of an inline view (marked in red) which acts as a temporary table holding the value for each row :
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 :
and finally grouped into :
Other SQL Workshops
More SQL Workshop soon!
To be informed about new articles on I Programmer, install the I Programmer Toolbar, subscribe to the RSS feed, follow us on, Twitter, Facebook, Google+ or Linkedin, or sign up for our weekly newsletter.
or email your comment to: firstname.lastname@example.org
|Last Updated ( Thursday, 06 September 2018 )|