#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2008
    Posts
    11
    Rep Power
    0

    Sum column in #table in Mssql Store Procedure


    Hi

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

    here is my sp

    Code:
    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
    My current result is

    Code:
    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 column (total) in single value

    Maideen
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    something i don't understand...

    you calculate sum(Nett) into #tableSI, and then sum(-Nett) into #tableSR

    and then you sum them both in the UNION

    unless i misunderstand what's going on, these two totals are just going to cancel each other out

    care to comment?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2014
    Posts
    1
    Rep Power
    0

    Sum column in #table in MSSQL sp using Inner Join instead


    Hi Maideen
    I would not use UNION but rather do an inner join. That way you get all of your values for the same Code on a single line with the Total as the last column.
    I'm not sure if this is your intended result.

    CODE NAME SIHeadNett SRHeadNett RCHeadChqAmt DNHeadNett CNHeadNett TOTAL
    DT001 DAKNA TRADING SDN BHD -96546 -1590 -689 1060 94263 -3502

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[usp_Customer_Due_Until_Now_2]
    @Code nvarchar(30)
    AS
    BEGIN
    SET NOCOUNT ON;
    SELECT
    T1.CODE, T1.NAME, SIHeadNett, SRHeadNett, RCHeadChqAmt, DNHeadNett, CNHeadNett,
    (SIHeadNett+ SRHeadNett+ RCHeadChqAmt+ DNHeadNett+ CNHeadNett) as TOTAL
    INTO #TEMPTTL
    FROM
    (
    (SELECT
    Code, Name, SUM(Nett) AS SIHeadNett from [dbo].[SIHead] group by CODE,NAME
    ) T1
    INNER JOIN
    (select
    Code, Name, SUM(-Nett) AS SRHeadNett from [dbo].[SRHead] GROUP BY CODE,NAME
    ) T2 ON T1.CODE = T2.CODE
    INNER JOIN
    (
    select
    Code, Name, SUM(-CHQAMOUNT) AS RCHeadChqAmt from [dbo].[RCHead] GROUP BY CODE,NAME
    ) T3 ON T1.CODE = T3.CODE
    INNER JOIN
    (
    select
    Code, Name, SUM(NETT) AS DNHeadNett from [dbo].[DNHead] GROUP BY CODE,NAME
    ) T4 ON T1.CODE = T4.CODE
    INNER JOIN
    (
    select
    Code, Name, SUM(-NETT) AS CNHeadNett from [dbo].[CNHead] GROUP BY CODE,NAME
    ) T5 on t1.code = t5.code
    )
    SELECT * FROM #TEMPTTL
    drop table #TEMPTTL
    END
    GO

IMN logo majestic logo threadwatch logo seochat tools logo