Group by not working

Hello Ramco Ramco,

The error you are experiencing arises because SQL requires that all selected columns in a GROUP BY query must either be included in the GROUP BY clause or be aggregated. In your case, the DueDate is not included in the GROUP BY, which leads to the error.

To resolve this, you can either include DueDate in your GROUP BY clause or apply an aggregate function to it. Here’s a modified version of your query that groups by CardCode and CardName, while also using an aggregate function for the DueDate:

 

SELECT T1."CardCode", T1."CardName",
       CASE
           WHEN (DAYS_BETWEEN(MAX(T0."DueDate"), NOW())) + 1 < 31 THEN
               CASE
                   WHEN T0."SYSCred" <> 0 THEN -SUM(T0."SYSCred")
                   ELSE SUM(T0."SYSDeb")
               END
       END AS "0-30 days"
FROM JDT1 T0 
INNER JOIN OCRD T1 ON T1."CardCode" = T0."ShortName"
INNER JOIN OJDT T2 ON T2."TransId" = T0."TransId"
WHERE T1."U_MSME" = 'Y'
AND T1."CardType" = 'S'
GROUP BY T1."CardCode", T1."CardName"
ORDER BY T1."CardCode", MAX(T0."TaxDate");

I used MAX(T0."DueDate") to ensure that the DueDate is aggregated, thus resolving the error while maintaining the intended functionality of your query.

Hello Ramco,

You can use below query to do group by, You have not applied group by in your query.

SELECT T1."CardCode", 
       T1."CardName",
       CASE
           WHEN (DAYS_BETWEEN(T0."DueDate", now())) + 1 < 31 THEN
               CASE
                   WHEN T0."SYSCred" <> 0 THEN -T0."SYSCred"
                   ELSE T0."SYSDeb"
               END
       END AS "0-30 days"
FROM JDT1 T0
INNER JOIN OCRD T1 ON T1."CardCode" = T0."ShortName"
INNER JOIN OJDT T2 ON T2."TransId" = T0."TransId"
WHERE T1."U_MSME" = 'Y'
AND T1."CardType" = 'S'
GROUP BY T1."CardCode", T1."CardName", T0."DueDate"  -- Grouping by DueDate
ORDER BY T1."CardCode", T0."TaxDate";