Aggregate function not allowed

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"

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:

1. Aggregate in HAVING: The subquery (SELECT COUNT(A1."BPLId")...) is now placed in the HAVING clause, as it applies to the grouped result.

2. 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.

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";