how to sum the column in #table temp table in mssql store procedure

[Maideen]

Hi
I do not know how to sum the one of the column in #table temp table

It is my SP

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_Customer_Due_Until_Now]
 @Code nvarchar(30)
AS
BEGIN

SET NOCOUNT ON;
select code as Code, Name as Name, sum(Nett) SINETT into #tableSI  From SIHead Where Code=@code Group By Code,Name
select code as Code, Name as Name, sum(-Nett) SINETT into #tableSR  From SRHead Where Code=@code Group By Code,Name
select code as Code, Name as Name, sum(-ChqAmount) SINETT into #tableRC  From RCHead Where Code=@code Group By Code,Name
select code as Code, Name as Name, sum(Nett) SINETT  into #tableDN  From DNHead  Where Code=@code Group By Code,Name
select code as Code, Name as Name, sum(-Nett) SINETT  into #tableCN  From CNHead Where Code=@code Group By Code,Name

select code,name,SUM(SINETT) AS TOTAL from #tableSI GROUP BY CODE,NAME
UNION
select code,name,SUM(SINETT) AS TOTAL from #tableSR GROUP BY CODE,NAME
UNION
select code,name,SUM(SINETT) AS TOTAL from #tableRC GROUP BY CODE,NAME
UNION
select code,name,SUM(SINETT) AS TOTAL from #tableDN GROUP BY CODE,NAME
UNION
select code,name,SUM(SINETT) AS TOTAL from #tableCN GROUP BY CODE,NAME


drop table #tableSI
drop table #tableSr
drop table #tablerc
drop table #tableDN
drop table #tableCN
END

Current result is

Code    Name                    Total   

DT001   DAKNA TRADING SDN BHD   -96545.61
DT001   DAKNA TRADING SDN BHD   -1590
DT001   DAKNA TRADING SDN BHD   -689
DT001   DAKNA TRADING SDN BHD   1060
DT001   DAKNA TRADING SDN BHD   94263.21

Pls advice me how to sum total column
pls help me
maideen

[Taywin]

Hmm... Maybe this post could help...

[ExpertMind]

DECLARE @tblSum AS TABLE (CODE VARCHAR(30),
                          NAME VARCHAR(100),
                          Total NUMERIC(18,0))


INSERT INTO @tblSum (code,Name,Total)
select code,name,SUM(SINETT) AS TOTAL from #tableSI GROUP BY CODE,NAME
UNION
select code,name,SUM(SINETT) AS TOTAL from #tableSR GROUP BY CODE,NAME
UNION
select code,name,SUM(SINETT) AS TOTAL from #tableRC GROUP BY CODE,NAME
UNION
select code,name,SUM(SINETT) AS TOTAL from #tableDN GROUP BY CODE,NAME
UNION
select code,name,SUM(SINETT) AS TOTAL from #tableCN GROUP BY CODE,NAME


SELECT SUM(TOTAL) As total FROM @tblSum