CODEWITHSUNDEEP
sqlasp.netsql-servert-sqlgridview
Aamir Shah
Aamir Shah

Reputation: 83

How to get sum totals of columns in SQL?

I have written a stored procedure which returns me effects on two accounts when I give Invoice no. as an input parameter.

SELECT A.AccountTitle,
       T.VoucherNumber, 
       T.ChequeNumber, 
       T.Particulars, 
       T.DR, 
       T.CR, 
       WU.UserName as Operator
FROM   dbo.Accounts A
INNER JOIN dbo.Transactions T ON T.Account_ID= A.AccountID
INNER JOIN dbo.Web_Users WU ON WU.UserID= T.User_ID
WHERE T.InvoiceNo= @InvoiceNo

Output:

AccountTitle    VoucherNumber   ChequeNumber    Particulars DR  CR  Operator
Conductor       NULL        NULL                New entry   100 0   Irfan
Jamal Acount    NULL        NULL                New entry1  0   100 Irfan

but I want totals of DR and CR field at the end.

Expected output

 AccountTitle   VoucherNumber   ChequeNumber    Particulars DR  CR  Operator
 Conductor      NULL           NULL             New entry   100 0   Irfan
 Jamal Acount   NULL           NULL             New entry1  0   100 Irfan
                                                    Total   100 100

Upvotes: 1

Views: 91

Answers (2)

Rigel1121
Rigel1121

Reputation: 2034

You can achieve your desired result using WITH ROLLUP. See below:

SELECT
     CASE WHEN GROUPING(T.Particulars)=1 THEN '' ELSE MIN(A.AccountTitle) END AccountTitle,
     CASE WHEN GROUPING(T.Particulars)=1 THEN '' ELSE MIN(T.VoucherNumber) END VoucherNumber,
     CASE WHEN GROUPING(T.Particulars)=1 THEN '' ELSE MIN(T.ChequeNumber) END ChequeNumber,
     CASE WHEN GROUPING(T.Particulars)=1 THEN 'TOTAL' ELSE MIN(T.Particulars) END Particulars, 
     SUM(CONVERT(MONEY,T.DR)) AS DR, 
     SUM(CONVERT(MONEY,T.CR)) AS CR,
     CASE WHEN GROUPING(T.Particulars)=1 THEN '' ELSE WU.UserName END Operator
FROM dbo.Accounts A
INNER JOIN dbo.Transactions T ON T.Account_ID= A.AccountID
INNER JOIN dbo.Web_Users WU ON WU.UserID= T.User_ID
WHERE T.InvoiceNo= @InvoiceNo
GROUP BY WU.UserName WITH ROLLUP

Upvotes: 2

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

You can use SUM function to count total DR and CR, empty values for other columns and UNION ALL opterator to combain result sets of the 2 select statements.

Something like this:

SELECT A.AccountTitle,
       T.VoucherNumber, 
       T.ChequeNumber, 
       T.Particulars, 
       T.DR, 
       T.CR, 
       WU.UserName as Operator
FROM   dbo.Accounts A
INNER JOIN dbo.Transactions T ON T.Account_ID = A.AccountID
INNER JOIN dbo.Web_Users WU   ON WU.UserID= T.User_ID
WHERE  T.InvoiceNo= @InvoiceNo
UNION ALL    
SELECT ''        AS AccountTitle, 
       NULL      AS VoucherNumber, 
       NULL      AS ChequeNumber, 
       'Total'   AS Particulars,
       CAST(SUM(T.DR) AS MONEY) AS DR,
       CAST(SUM(T.CR) AS MONEY) AS CR,
       ''        AS Operator
FROM   dbo.Accounts A
INNER JOIN dbo.Transactions T ON T.Account_ID= A.AccountID
INNER JOIN dbo.Web_Users WU   ON WU.UserID= T.User_ID
WHERE  T.InvoiceNo= @InvoiceNo

Upvotes: 1

Related Questions