Group by G/l Name

Question

Hi

I want below as inner query then i want to do Group by G/l Account only.

SELECT T0."TransId",T1."TransType", T1."Account",Max(T2."AcctName") As "G/l Name",Sum(T1."Debit") As "Debit", Sum(T1."Credit") As "Credit",

CASE

WHEN T1."TransType" = '13' THEN (Select max(A0."LocCode") from INV1 A0 where A0."DocEntry" = T1."CreatedBy")

WHEN T1."TransType" = '14' THEN (Select max(A0."LocCode") from RIN1 A0 where A0."DocEntry" = T1."CreatedBy")


Else '0'

END AS "Location"

 

FROM OJDT T0  INNER JOIN JDT1 T1 ON T0."TransId" = T1."TransId"

Left Join OACT T2 on T2."AcctCode" = T1."Account"

WHERE T1."BPLId" = 28

group by T0."TransId",T1."TransType",T1."Account",T1."CreatedBy"

ORDER BY T1."TransType"

Thanks

Amira Bedhiafi · Answer

WITH InnerQuery AS ( SELECT T0.TransId T1.TransType T1.Account MAX(T2.AcctName) AS G/l Name SUM(T1.Debit) AS Debit SUM(T1.Credit) AS Credit CASE WHEN T1.TransType = '13' THEN (SELECT MAX(A0.LocCode) FROM INV1 A0 WHERE A0.DocEntry = T1.CreatedBy) WHEN T1.TransType = '14' THEN (SELECT MAX(A0.LocCode) FROM RIN1 A0 WHERE A0.DocEntry = T1.CreatedBy) ELSE '0' END AS Location FROM OJDT AS T0 INNER JOIN JDT1 AS T1 ON T0.TransId = T1.TransId LEFT JOIN OACT AS T2 ON T2.AcctCode = T1.Account WHERE T1.BPLId = 28 GROUP BY T0.TransId T1.TransType T1.Account T1.CreatedBy ) SELECT Account AS G/l Account MAX(G/l Name) AS G/l Name SUM(Debit) AS Total Debit SUM(Credit) AS Total Credit MAX(Location) AS Primary Location FROM InnerQuery GROUP BY Account ORDER BY Account You can use a CTE for the inner query

Prasad Raveendran · Answer

please check whether this would help WITH FilteredData AS ( SELECT T1.Account T1.TransType T1.Debit T1.Credit T1.CreatedBy FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId WHERE T1.BPLId = 28 ) LocationData AS ( SELECT A0.DocEntry MAX(A0.LocCode) AS LocCode FROM INV1 A0 GROUP BY A0.DocEntry UNION ALL SELECT A0.DocEntry MAX(A0.LocCode) AS LocCode FROM RIN1 A0 GROUP BY A0.DocEntry ) SELECT F.Account AS G/L Account MAX(ACT.AcctName) AS G/L Name SUM(F.Debit) AS Total Debit SUM(F.Credit) AS Total Credit COALESCE(L.LocCode '0') AS Location FROM FilteredData F LEFT JOIN OACT ACT ON ACT.AcctCode = F.Account LEFT JOIN LocationData L ON L.DocEntry = F.CreatedBy GROUP BY F.Account ORDER BY F.Account;

Muhammad Imran Ansari · Answer

Hi Ramco To group the results by  G/L Name  instead of the individual transaction ID (TransId) you should adjust the GROUP BY clause to include only the fields necessary for aggregation. Here’s the updated query: SELECT T1.Account AS G/L Account MAX(T2.AcctName) AS G/L Name SUM(T1.Debit) AS Total Debit SUM(T1.Credit) AS Total Credit CASE WHEN T1.TransType = '13' THEN MAX(A1.LocCode) WHEN T1.TransType = '14' THEN MAX(A2.LocCode) ELSE '0' END AS Location FROM JDT1 T1 LEFT JOIN OACT T2 ON T2.AcctCode = T1.Account LEFT JOIN INV1 A1 ON A1.DocEntry = T1.CreatedBy AND T1.TransType = '13' LEFT JOIN RIN1 A2 ON A2.DocEntry = T1.CreatedBy AND T1.TransType = '14' WHERE T1.BPLId = 28 GROUP BY T1.Account ORDER BY T1.Account;