java.sql.SQLException: Invalid use of group function

Question

From this query:

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

I am getting this error:

java.sql.SQLException: Invalid use of group function

What is the issue?

Answer 1

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