how to sum in #table column in mssql store procedure

Question

0.00/5 (No votes)

See more:

Hi

I do not know how to sum one of the column (TOTAL) in temp table

SQL

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

SQL

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 in single line

Pls help me.

Maideen

Posted 16-Dec-14 23:14pm

Maideen Abdul Kader

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

KM Perumal · Accepted Answer · 2014-12-16T23:26:00

Solution 1

Try this ...

SQL

select t1.code,t1.name,(SUM(t1.SINETT)+SUM(t2.SINETT)+SUM(t3.SINETT)+SUM(t4.SINETT)SUM(t5.SINETT)) AS TOTAL from #tableSI t1 
inner join #tableSR t2 on t1.code=t2.code
inner join #tableRC t3 on t1.code=t3.code
inner join #tableDN t4 on t1.code=t4.code
inner join #tableCN  t5 on t1.code=t5.code

Posted 16-Dec-14 23:26pm

KM Perumal

Comments

Maideen Abdul Kader 17-Dec-14 5:52am

thanks

both KM Perumal and Tomas takac code is working fine

Thank you very much both

madieen

KM Perumal 17-Dec-14 6:54am

Thanks

Maideen Abdul Kader 17-Dec-14 6:55am

Hi KM Perumal.
I am using your code. Thanks working fine. But If any table does not have data, the result not appear
Example if #tableRc does not have data for the particular client, the result shows nothing.
Pls help me in this issue
Maideen

Tomas Takac · Accepted Answer · 2014-12-16T23:33:00

You can wrap your results in a CTE and then use it to sum the totals like this:

SQL

with results as
(
  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 * from results
union all
select '' as code, 'grand total' as name, sum(total) as total from results

This will give you this result:

code  name                  total
----- --------------------- ---------------------------------------
DT001 DAKNA TRADING SDN BHD -96545.61
DT001 DAKNA TRADING SDN BHD -1590.00
DT001 DAKNA TRADING SDN BHD -689.00
DT001 DAKNA TRADING SDN BHD 1060.00
DT001 DAKNA TRADING SDN BHD 94263.21
      grand total           -3501.40