Member Avatar for 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

  • 3 Contributors
  • 2 Replies
  • 2K Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by ExpertMind

Dani AI

Generated

You are getting 5 rows because each SELECT returns a subtotal per source table. To get the final balance per customer, push all rows into one set with UNION ALL and then aggregate once more. Also, keep the fractional cents; do not use a scale of 0 or you will truncate values like 96545.61.

Here is a clean approach without temp tables:

;with movements as (
    select Code, Name, sum(Nett)        as amt from SIHead where Code = @Code group by Code, Name
    union all
    select Code, Name, sum(-Nett)       from SRHead where Code = @Code group by Code, Name
    union all
    select Code, Name, sum(-ChqAmount)  from RCHead where Code = @Code group by Code, Name
    union all
    select Code, Name, sum(Nett)        from DNHead where Code = @Code group by Code, Name
    union all
    select Code, Name, sum(-Nett)       from CNHead where Code = @Code group by Code, Name
)
select
    Code,
    Name,
    cast(sum(amt) as decimal(19,2)) as Total
from movements
group by Code, Name;

Notes and gotchas:

  • Use UNION ALL, not UNION. You want every row to contribute to the final SUM and you do not want the implicit distinct/sort overhead of UNION.
  • ’s idea of summing after inserting into a table variable works too, but keep decimals (e.g., numeric(19,2)) and, if you need totals per customer, group by Code/Name in the final SELECT instead of returning a single grand total.
  • Be careful grouping by Name across multiple tables. If the name text differs even slightly, you will get multiple rows. Consider grouping by Code only, or joining to a single Customers table to fetch the canonical Name.
  • For speed, an index on each head table that starts with Code (and includes the amount column) helps, e.g., SIHead: index on (Code) include (Name, Nett).

Recommended Answers

All 2 Replies

Member Avatar for Taywin

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

Member Avatar for 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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.