java.sql.SQLException:组函数的使用无效

Question

来自此查询：

select count(*) as data, LOCATION_Name as location 
from SPECIAL_PROCEDURE_COLLECTION_FINAL_temp_final 
where sno>=6973 
and sno<= 7251 
and date(PAYMENT_DATE) = '2021-07-31' 
and Procedures_Category IN ('FFA-VR') 
and SUM(BILLED_AMOUNT)= 0 
group by LOCATION_Name

我得到这个错误：

java.sql.SQLException：组函数的使用无效

问题出在哪里？

Answer 1

不能在where子句中使用聚合函数。请将您的查询更改为：

select count(*) as data, LOCATION_Name as location 
from SPECIAL_PROCEDURE_COLLECTION_FINAL_temp_final 
where sno>=6973 and sno<= 7251 and 
date(PAYMENT_DATE) = '2021-07-31' and Procedures_Category IN ('FFA-VR') 
group by LOCATION_Name
Having SUM(BILLED_AMOUNT)= 0

Answer 2

对账单金额总和的限制属于HAVING条款，而不是WHERE条款：

SELECT COUNT(*) AS data, LOCATION_Name AS location 
FROM SPECIAL_PROCEDURE_COLLECTION_FINAL_temp_final 
WHERE sno BETWEEN 6973 AND 7251 AND
PAYMENT_DATE >= '2021-07-31' AND PAYMENT_DATE < '2021-08-01' AND
Procedures_Category = 'FFA-VR'
GROUP BY LOCATION_Name
HAVING SUM(BILLED_AMOUNT) = 0;