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,

Solution 1

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

Solution 2

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)

Solution 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

Solution 4

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.

View more solutions

Comments

[tobyodavies]

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

[novicePrgrmr]

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

[novicePrgrmr]

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]

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

[tobyodavies]

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