How to display Sum of last 2 columns

Please check the below approach:

WITH CombinedData AS (
    -- Combine the main data from both queries
    SELECT  
        T3."ShortName" AS "Vendor Code",
        (SELECT MAX("CardName") FROM OCRD WHERE "CardCode" = T3."ShortName") AS "Vendor Name",
        T3."Debit", 
        T3."Credit"
    FROM OJDT T2 
    INNER JOIN JDT1 T3 ON T2."TransId" = T3."TransId" 
    WHERE T3."ShortName" = :Vendor 

    UNION ALL

    SELECT  
        T2."CardCode" AS "Vendor Code",
        T2."CardName" AS "Vendor Name",
        (T2."DocTotal") + (T2."DpmAmnt") + (T2."DpmVat") AS "Debit",
        0 AS "Credit"
    FROM OINV T2
    WHERE T2."CardCode" = :Vendor
)
-- Final result with totals
SELECT 
    "Vendor Code", 
    "Vendor Name", 
    "Debit", 
    "Credit"
FROM CombinedData

UNION ALL

SELECT 
    'TOTAL' AS "Vendor Code", 
    NULL AS "Vendor Name", 
    SUM("Debit") AS "Debit", 
    SUM("Credit") AS "Credit"
FROM CombinedData;

Hello Ramco Ramco,

To achieve the desired outcome of displaying the sum of the last two columns at the bottom of your SQL query results, you can utilize a UNION ALL to append a summary row. Here’s how you can modify your existing query:

SELECT T3."ShortName" AS "Vendor Code",
       (SELECT Max("CardName") FROM OCRD WHERE "CardCode" = T3."ShortName") AS "Vendor Name",
       T3."Debit", 
       T3."Credit"
FROM OJDT T2 
INNER JOIN JDT1 T3 ON T2."TransId" = T3."TransId" 
WHERE (T3."ShortName" = :Vendor)

UNION ALL

SELECT T2."CardCode" AS "Vendor Code",
       T2."CardName" AS "Vendor Name",
       (T2."DocTotal") + (T2."DpmAmnt") + (T2."DpmVat") AS "Debit",
       0 AS "Credit"
FROM OINV T2
WHERE (T2."CardCode" = :Vendor)

UNION ALL

SELECT 'Total' AS "Vendor Code",
       NULL AS "Vendor Name",
       SUM(T3."Debit") AS "Debit",
       SUM(T3."Credit") AS "Credit"
FROM (
    SELECT T3."Debit", T3."Credit"
    FROM OJDT T2 
    INNER JOIN JDT1 T3 ON T2."TransId" = T3."TransId" 
    WHERE (T3."ShortName" = :Vendor

    UNION ALL

    SELECT (T2."DocTotal") + (T2."DpmAmnt") + (T2."DpmVat") AS "Debit",
           0 AS "Credit"
    FROM OINV T2
    WHERE (T2."CardCode" = :Vendor)
) AS T3;

In this modified query, the last UNION ALL statement calculates the total sums of the "Debit" and "Credit" columns and labels the row as "Total". This approach ensures that the summary appears at the bottom of your result set, providing a clear overview of the financial data.

Hello Ramco,

please use below query to get desire out put,

SELECT  T3."ShortName" AS "Vendor Code",
        (SELECT MAX("CardName") FROM OCRD WHERE "CardCode" = T3."ShortName") AS "Vendor Name",
        T3."Debit", 
        T3."Credit"
FROM OJDT T2 
INNER JOIN JDT1 T3 ON T2."TransId" = T3."TransId" 
WHERE T3."ShortName"=:Vendor

UNION ALL

SELECT T2."CardCode" AS "Vendor Code",
       T2."CardName" AS "Vendor Name",
       (T2."DocTotal") + (T2."DpmAmnt") + (T2."DpmVat") AS "Debit",
       0 AS "Credit"
FROM OINV T2
WHERE T2."CardCode"=:Vendor

UNION ALL

SELECT 'Total' AS "Vendor Code",
       NULL AS "Vendor Name",
       SUM("Debit") AS "Debit",
       SUM("Credit") AS "Credit"
FROM (
    SELECT T3."Debit", T3."Credit"
    FROM OJDT T2
    INNER JOIN JDT1 T3 ON T2."TransId" = T3."TransId" 
    WHERE T3."ShortName"=:Vendor

    UNION ALL

    SELECT (T2."DocTotal") + (T2."DpmAmnt") + (T2."DpmVat") AS "Debit",
           0 AS "Credit"
    FROM OINV T2
    WHERE T2."CardCode"=:Vendor
) AS Combined

To display the sum of the last two columns (Debit and Credit) in your query, you can add a calculated column to perform the addition. The SQL syntax for the SUM calculation will depend on the database you're using (e.g., SAP HANA, SQL Server, etc.). Here's the adjusted query:

Updated Query:

SELECT  
    T3."ShortName" AS "Vendor Code",
    (SELECT MAX("CardName") FROM OCRD WHERE "CardCode" = T3."ShortName") AS "Vendor Name",
    T3."Debit",
    T3."Credit",
    (T3."Debit" + T3."Credit") AS "Total"
FROM 
    OJDT T2 
    INNER JOIN JDT1 T3 ON T2."TransId" = T3."TransId" 
WHERE
    T3."ShortName" = :Vendor

UNION ALL

SELECT  
    T2."CardCode" AS "Vendor Code",
    T2."CardName" AS "Vendor Name",
    (T2."DocTotal" + T2."DpmAmnt" + T2."DpmVat") AS "Debit",
    0 AS "Credit",
    (T2."DocTotal" + T2."DpmAmnt" + T2."DpmVat" + 0) AS "Total"
FROM 
    OINV T2
WHERE 
    T2."CardCode" = :Vendor

Explanation of Changes:

1. New Column Total:

   • In the first part of the query:

     (T3."Debit" + T3."Credit") AS "Total"
     

   • In the second part of the query:

     (T2."DocTotal" + T2."DpmAmnt" + T2."DpmVat" + 0) AS "Total"
     

2. Consistency Across UNION:

   • The UNION ALL operation requires all SELECT statements to have the same number of columns and compatible data types.

3. Naming Conventions:

   • Total column is added to display the sum of Debit and Credit in the first part and to maintain compatibility in the second part.

This query now calculates and displays the sum of the last two columns as a new column labeled Total.