Aggregate function not allowed

Question

Hi

I have below query and i am getting error - Aggregate function not allowed

SELECT T1."TransId", Max(T1."RefDate") FROM OJDT T0  INNER JOIN JDT1 T1 ON T0."TransId" = T1."TransId" WHERE (Select Count(T1."BPLId") from JDT1 A1 where A1."TransId" = T1."TransId") > 0
group By T1."TransId"

Thanks

Amit Mohanty · Answer

You are getting the error because you are trying to use an aggregate function (Max) alongside a subquery within the WHERE clause. SQL Server does not allow mixing aggregate functions with certain types of subqueries. Once try the below query: SELECT T1.TransId MAX(T1.RefDate) AS MaxRefDate FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId WHERE T1.TransId IN ( SELECT A1.TransId FROM JDT1 A1 WHERE A1.BPLId IS NOT NULL GROUP BY A1.TransId HAVING COUNT(A1.BPLId) > 0 ) GROUP BY T1.TransId;

Muhammad Imran Ansari · Answer

Hi Ramco In your subquery you are using directly Count() but SQL Server does not allow the use of an aggregate function (e.g. COUNT()) within a WHERE clause. To fix this issue you need to refactor the query to use CTE. Using CTE you can enhance the readability and separates filtering logic. Here is the query: WITH FilteredTransIds AS ( SELECT A1.TransId FROM JDT1 A1 GROUP BY A1.TransId HAVING COUNT(A1.BPLId) > 0 ) SELECT T1.TransId MAX(T1.RefDate) AS MaxRefDate FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId WHERE T1.TransId IN (SELECT TransId FROM FilteredTransIds) GROUP BY T1.TransId

Jaish Mathews · Answer

The error occurs because of the aggregate function usage in a WHERE clause. SQL does not allow aggregate functions (like COUNT() ) directly in the WHERE clause. Instead you need to use a HAVING clause for such conditions as it is evaluated after grouping. Here’s the corrected query: SELECT T1.TransId MAX(T1.RefDate) AS MaxRefDate FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId GROUP BY T1.TransId HAVING (SELECT COUNT(A1.BPLId) FROM JDT1 A1 WHERE A1.TransId = T1.TransId) > 0 Key Changes: Aggregate in HAVING : The subquery (SELECT COUNT(A1.BPLId)...) is now placed in the HAVING clause as it applies to the grouped result. MAX function : The MAX(T1.RefDate) works fine in the SELECT statement as it aligns with the GROUP BY . Explanation: GROUP BY T1.TransId ensures each TransId is processed as a group. MAX(T1.RefDate) retrieves the maximum RefDate for each group. The HAVING clause filters out groups where the subquery’s COUNT result is not greater than 0. Notes: If the subquery result (COUNT(A1.BPLId)) could be calculated as part of the main query consider optimizing the query by joining the necessary tables rather than using a subquery.