# SQL problem with aggregate functions in where clause

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