Dear Expert,
I have problem this query below. I have grouping function to split group and show subtotal and grandtotal but it's grandtotal wrong position to top row (Red) what i want to below row (green).. Please you resolve to me...
Code :
SELECT
Case When Grouping(A."Area") = 1 and Grouping(A."Code") = 1 and Grouping(A."Name") = 1 and Grouping(A."Sales") = 0 Then 'SubTotal'
When Grouping(A."Area") = 1 and Grouping(A."Code") = 1 and Grouping(A."Name") = 1 and Grouping(A."Sales") = 1 Then 'GrandTotal'
Else Cast(A."Code" as varchar(10)) end as "Code",
A."Name",
A."Area",
Case when A."Name" is null then Null Else A."Sales" end as "Sales",
Sum("TO") as "Total",
Sum("PF") as "Profit"
FROM
(
SELECT
T0."CardCode" as "Code",
T0."CardName" as "Name",
T2."U_CUSTOMER_AREA" as "Area",
T3."SlpName" as "Sales",
SUM(T1."LineTotal")-ROUND(((SUM(T1."LineTotal"))*(T0."DiscPrcnt"/100)),2,ROUND_HALF_UP) as "TO",
SUM(T1."GrssProfSC") as "PF"
FROM
ORIN T0
INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry"
LEFT JOIN OCRD T2 ON T0."CardCode" = T2."CardCode"
LEFT JOIN OSLP T3 ON T0."SlpCode" = T3."SlpCode"
LEFT JOIN "BEC2"."@SALESTEAM" T4 ON T3."U_Sale_Team" = T4."Code"
WHERE
T0."CANCELED" = 'N' and
T4."Code" = '11160' and
T0."DocDate" between '01.07.2023' and '31.07.2023' and
T1."AcctCode" = '4011010' and
T1."LineTotal" > 0
GROUP BY
T0."CardCode", T0."CardName", T2."U_CUSTOMER_AREA",T4."Code", T3."SlpName",T0."DiscPrcnt"
ORDER BY
T0."CardCode"
) A
Group by
GROUPING SETS
(
(A."Sales"),
(A."Code",A."Name",A."Area",A."Sales"),()
)
ORDER By
A."Sales",
Sum("TO")
