SQL problem with aggregate functions in where clause


Answers

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







Tags