SQL problem with aggregate functions in where clause

Question

I am working on this SQL problem:

Show any purchase orders whose charged amount and actual amount are different. Show this by displaying the purchase order number, the POAmount for each purchase order, the actual amount (calculated by adding the prices of all items in the order), and the difference between the two. Sort the results to show those with the largest differences first.

I am getting the following code when running the sql statement below:

Error code -1, SQL state 42903: Invalid use of an aggregate function.

select 
  purchaseorder.ponum, 
  purchaseorder.amount, 
  sum(poitems.quantity*poitems.unitprice), 
  purchaseorder.amount-sum(poitems.quantity*poitems.unitprice)
from  purchaseorder, poitems
where 
  purchaseorder.ponum = poitems.ponum 
    and purchaseorder.amount!=sum(poitems.quantity*poitems.unitprice)
group by 
  purchaseorder.ponum, 
  purchaseorder.amount

I think it's because I'm using an aggregate function in my where clause.

How can I remedy this problem???

Thanks,

John Hartsock · Accepted Answer · 2010-11-03 23:58:24Z

5

Try this:

select 
  purchaseorder.ponum, 
  purchaseorder.amount, 
  sum(poitems.quantity*poitems.unitprice),   
  purchaseorder.amount-sum(poitems.quantity*poitems.unitprice) 
from  purchaseorder, poitems 
where 
  purchaseorder.ponum = poitems.ponum 
group by 
  purchaseorder.ponum, 
  purchaseorder.amount 
having  
  purchaseorder.amount!=sum(poitems.quantity*poitems.unitprice)

edited Nov 3, 2010 at 23:58

John Hartsock

87.1k23 gold badges135 silver badges146 bronze badges

answered Nov 3, 2010 at 23:55

Jeff Hornby

13.8k4 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tobyodavies Over a year ago

That doesn't do the ordering he requested - with highest diff first ;)

tobyodavies · Accepted Answer · 2010-11-04 01:15:54Z

3

You can't have an aggregate in a where clause - that's what having is for - change to

select purchaseorder.ponum,
       purchaseorder.amount,
       sum(poitems.quantity*poitems.unitprice) as actual,
       purchaseorder.amount - sum(poitems.quantity*poitems.unitprice) as diff
from  purchaseorder,
      poitems
where purchaseorder.ponum = poitems.ponum
group by purchaseorder.ponum,
         purchaseorder.diff
having diff != 0
order by ABS(diff) desc

edited Nov 4, 2010 at 1:15

answered Nov 3, 2010 at 23:56

tobyodavies

28.2k5 gold badges45 silver badges58 bronze badges

3 Comments

novicePrgrmr Over a year ago

Thanks for seeing that I need it ordered. I'm getting this error when using your code though:

novicePrgrmr Over a year ago

Error code -1, SQL state 42X04: Column 'DIFF' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'DIFF' is not a column in the target table.

tobyodavies Over a year ago

see if the edit works (i just did what the error said to do, still untested :P)

Daniel Vassallo · Accepted Answer · 2010-11-03 23:56:56Z

It looks like you should use the HAVING clause instead:

SELECT    ...
FROM      purchaseorder
JOIN      poitems ON (purchaseorder.ponum = poitems.ponum)
GROUP BY  purchaseorder.ponum, purchaseorder.amount
HAVING    purchaseorder.amount != sum(poitems.quantity * poitems.unitprice);

The HAVING clause is applied nearly last, and is there mainly for filtering on aggregate functions. LIMIT is applied after HAVING.

Note that the SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well.

As a side-note, you may also want to use an explicit Inner Join in there (as shown in my example) instead of the old ANSI-89 syntax that you are using.

novicePrgrmr · Accepted Answer · 2010-11-04 00:44:12Z

0

Here's what worked for me. Thanks guys!

select purchaseorder.ponum,
   purchaseorder.amount,
   sum(poitems.quantity*poitems.unitprice) as actual,
   purchaseorder.amount - sum(poitems.quantity*poitems.unitprice) as "diff"
FROM      purchaseorder
JOIN      poitems ON (purchaseorder.ponum = poitems.ponum)
GROUP BY  purchaseorder.ponum, purchaseorder.amount
HAVING    purchaseorder.amount != sum(poitems.quantity * poitems.unitprice)
order by "diff" desc

answered Nov 4, 2010 at 0:44

novicePrgrmr

19.5k31 gold badges85 silver badges104 bronze badges

1 Comment

tobyodavies Over a year ago

is amount always greater than actual? otherwise you might want order by ABS(diff) or something similar dependant on your flavour of SQL

Collectives™ on Stack Overflow

SQL problem with aggregate functions in where clause

4 Answers 4

1 Comment

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related