Group by G/l Name

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

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