java.sql.SQLException: Invalid use of group function-CodePudding

from this query i am getting the issue "java.sql.SQLException: Invalid use of group function"

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

CodePudding user response：

You cannot use an aggregate function in the where clause. Kindly change yor query to:

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

CodePudding user response：

The restriction on the sum of the billed amount belongs in the HAVING clause, rather than the WHERE clause:

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;