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 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

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!

Related Articles

SQL Server: Quickly get row counts for tables, heaps, indexes, and partitions

The First Things I Look At On A SQL Server – Part 1

The First Things I Look At On A SQL Server – Part 2

A Generic SQL Server Compression Utility

Codd and his Rules      

 

To be informed about new articles on I Programmer, install the I Programmer Toolbar, subscribe to the RSS feed, follow us on, Twitter, FacebookGoogle+ or Linkedin,  or sign up for our weekly newsletter.

 SQLW

 

blog comments powered by Disqus

 

Banner


Mozilla Labs Closed And Nobody Noticed - UPDATE Mozilla Responds
23/09/2014

A blog post by Ian Bickling conveyed the news that mozilla Labs has closed. A big and disturbing event for web development. But this happened months ago and this is the first we had heard about it.



Windows Not 9 But 10
01/10/2014

The next version of Windows has just been presented to the world and  the biggest surprise is that it is to be called Windows 10 rather than the more logical Windows 9.


More News

 



Last Updated ( Thursday, 19 December 2013 )
 
 

   
RSS feed of all content
I Programmer - full contents
Copyright © 2014 i-programmer.info. All Rights Reserved.
Joomla! is Free Software released under the GNU/GPL License.