Group by not working

Question

Hi

In below query i want to group by code,name. But when i group then it gives error DueDate is not grouped by.

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 "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' 
ORDER BY T1."CardCode", T0."TaxDate"

Thanks

Jayraj Chhaya · Answer

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

Jignesh Kumar · Answer

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