how to get the summation of column in the last row

Question

select csm.csmCustomerName, cur.curNameOfCurrency, 
sum(sot.sotItemTotalAmount)as 'TotalItemsAmount',
SUM(sorTotalTaxAmountValue) as 'TotalTax',
SUM(sorTotalChargeDetailsAmountValue) as 'TotalCharges',
(sum(sorTotalTaxAmountValue)+sum(sorTotalChargeDetailsAmountValue)+sum(sot.sotItemTotalAmount)) as 'NetAmount'
from dbo.SalesOrder sor join dbo.Currency cur
on sor.sorCurrencyId=cur.curId
join dbo.CustomerMaster csm
on sor.sorCustomerMasterId=csm.csmId
join SalesOrderItemDetails sot
on sot.sotSalesOrderId=sor.sorId
Group by csmCustomerName, curNameOfCurrency with rollup;

I want the sum of TotalItemsAmount, TotalTax, TotalCharges and NetAmount in the last row of respective columns. In result set, I get duplication of each row, could anyone correct the mistake in my code.

C1 C2 C3 C4 C5 C6

A USD 1 7 2 10

B USD 3 6 3 12

C USD 5 3 0 8

D USD 4 2 1 7

   13  18   6  37

Could you share some data and the result you're trying to get? — Mureinik, May 08 '15 at 09:37
Without appropriate same data and expected output, it is hard to answer . Are you looking for something like `WITH ROLLUP` — ughai, May 08 '15 at 09:48
To get summation of the column in its last row, I used GROUP BY and ROLL UP clause (shown in code), but duplication of each rows generated with NULL when I execute. I want to avoid the duplication. — Kavr, May 08 '15 at 09:55

score 0 · Accepted Answer · answered May 08 '15 at 10:09

It is done via the grouping sets like:

DECLARE @t TABLE ( code CHAR(3), a INT, b INT )

INSERT  INTO @t
VALUES  ( 'USD', 1, 2 ),
        ( 'USD', 5, 1 ),
        ( 'USD', 10, 7 ),
        ( 'EUR', 15, 13 )


SELECT  code ,
        SUM(a) AS a ,
        SUM(b) AS b
FROM    @t
GROUP BY GROUPING SETS(( code ), ( ))

Output:

code    a   b
EUR     15  13
USD     16  10
NULL    31  23

how to get the summation of column in the last row

1 Answers1