CODEWITHSUNDEEP
androidmysqlsqlsqlite
Seenu69
Seenu69

Reputation: 1054

SQLite Error - misuse of aggregate: sum()

Hi I'm getting misuse of aggregate: sum() error for the follwing query. Whats wrong with this.

> Select storeid, supplierid, ordernumber, inwardnumber, returnid,
> taxinvoiceno, ordervalue, sum(CASHPAID) as 'CASHPAID', balancedue,
> paidamt, paiddate, sum(ADJUSTMENT) as
> ADJUSTMENT,ordervalue+sum(ADJUSTMENT) as total from
> supplierpaymentstabledb where storeid = 10008 and total!=CASHPAID
> group by inwardnumber

Upvotes: 0

Views: 640

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

As a matter of good practice, you should put all the non-aggregated columns in the group by clause. Then, conditions on the aggregated columns should be in a having clause, not a where clause:

Select storeid, supplierid, ordernumber, inwardnumber, returnid,
       taxinvoiceno, ordervalue, sum(CASHPAID) as CASHPAID, balancedue,
       paidamt, paiddate, sum(ADJUSTMENT) as ADJUSTMENT, 
       ordervalue + sum(ADJUSTMENT) as total
from supplierpaymentstabledb
where storeid = 10008 
group by storeid, supplierid, ordernumber, inwardnumber, returnid,
         taxinvoiceno, ordervalue, balancedue,
         paidamt, paiddate
having total <> CASHPAID;

Upvotes: 3

Related Questions