How to display Sum of last 2 columns

Question

Hi

I have below query . I want to display sum of the last 2 columns at the Bottom.

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"),0
FROM OINV T2
WHERE (T2."CardCode"=:Vendor )

Thanks

Naveen Kumar · Answer

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;

Jayraj Chhaya · Answer

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.

Jignesh Kumar · Answer

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

Jaish Mathews · Answer

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: 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 Consistency Across UNION : The UNION ALL operation requires all SELECT statements to have the same number of columns and compatible data types. 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 .