android - SQLite 错误 - 滥用聚合 : sum()

Question

您好，我在以下查询中误用了 aggregate: sum() 错误。这有什么问题。

> 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

Answer 1

作为一种良好做法，您应该将所有非聚合列放在 group by 子句中。然后，聚合列的条件应该在 having 子句中，而不是 where 子句中:

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;