Summing Two Column values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Summing Two Column values

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2011-09-26 : 22:57:33

Greetings experts,

Can someone please help with this query.

I have 2 columns check and cash. Their values look similar to the following:

check Cash
29.00 00
298.00 00
00 120.00
etc

What I am trying to do is take the total of check and add it to the total of cash to get grandTotal.

For example, the total of check would be 327.00 and cash would be 120.00

GrandTotal would be 447.00 and I am looking for something similar.

Can an expert please take a look and see what i am doing wrong?


SELECT a.deptcode, c.deptname, em.check, em.cash,a.empl_first,a.empl_last, e.[e_mail],a.employee_id, sum(IsNull(em.check,0)) as CheckTotal, sum(IsNull(em.cash,0)) as cashTotal, sum(checkTotal + sum(cashTotal) as grandTotal  
FROM ctable c,[e_mail] e,Aempl a,EMamts em WHERE c.divisioncode = em.divisioncode  
AND a.empid = e.empid  
AND e.empid = em.empid   
AND em.empid = '11111'  
AND (isnull(em.check, 0)<> 0  
OR isnull(em.cash, 0)<> 0) Group by a.deptcode, c.deptname, em.check, em.cash,a.empl_first

Thanks alot in advance

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-26 : 23:18:47

I think there is a syntactical error in your script.
>>sum(checkTotal + sum(cashTotal)

Create table #tblSum
(
[Check] money,
[Cash] money
)
Go

Insert into #tblSum Values (29.00, 00)
Insert into #tblSum Values (298.00, 00)
Insert into #tblSum Values (00, 120.00)
GO

SELECT SUM([check]) + SUM([cash]) FROM #tblSum
GO

Best Regards
Vadivel

http://vadivel.blogspot.com

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2011-09-26 : 23:28:52

Thank you very much for your prompt response.

I am still getting the same values with your code that I get with mine.

For instance, I have a check amount of 10.00 and cash amount of 0.00.

When I add them up, I expect to get 10.00.

Instead, I am getting 100.00.

I don't know why.

The datatype for both is money.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-26 : 23:59:15

[code]
SELECT a.deptcode, c.deptname, em.check, em.cash, a.empl_first,
a.empl_last, e.[e_mail], a.employee_id,
sum(IsNull(em.check,0)) as CheckTotal,
sum(IsNull(em.cash,0)) as cashTotal,
sum(IsNull(em.check,0)) + sum(IsNull(em.cash,0)) as grandTotal
FROM ctable c
inner join EMamts em on c.divisioncode = em.divisioncode
inner join [e_mail] e on e.empid = em.empid
inner join Aempl a on a.empid = e.empid

WHERE em.empid = '11111'
AND (
isnull(em.check, 0) <> 0
OR isnull(em.cash, 0) <> 0
)
Group by a.deptcode, c.deptname, em.check, em.cash, a.empl_first,
a.empl_last, e.[e_mail], a.employee_id
[/code]

KH
[spoiler]Time is always against us[/spoiler]

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2011-09-27 : 08:52:16

Thanks a lot Khan,

I am still getting the same weird result.

If check amount is 10.00 and cash is 0.00 and I add them up, instead of getting 10.00, I am still getting 100.00.

I think I may have found why.

On the db, check and cash are displayed as:

0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
20.0000

Is it possible that the 2 extra 00s is the problem?

If I do this:

sum(IsNull(em.check_amt,0))/10 as CheckTotal, I get the right value.

Is there some function or script I can use that will get rid of the 2 extra 00s without having to divide by 10?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-27 : 10:15:21

it does not make sense.

Could it be you missed out some join condition between the table ?

double check this


FROM 	ctable c
	inner join EMamts em 	on	c.divisioncode = em.divisioncode  
	inner join [e_mail] e	on	e.empid = em.empid   
	inner join Aempl a	on	a.empid = e.empid

KH
[spoiler]Time is always against us[/spoiler]

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2011-09-27 : 11:15:55

You are right Khtan,

If I do the join you just asked me to do with the following:

AND (isnull(em.check, 0)<> 0
OR isnull(em.cash, 0)<> 0)

It comes out exactly right.

If I include the above, then the extra 2 00s.

Weird!

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2011-09-27 : 11:49:46

Sorry this forum doesn't have an edit feature, so I have to post another comment.

If you remove this entire WHERE clause:

WHERE em.empid = '11111' 
  AND (IsNull(em.check,0)<> 0 
  OR IsNull(em.cash,0)<> 0)
  group by c.charity_name, 
  em.check, em.cash,e.[e_mail]

everything works as expected.

I must be doing something wrong with the where clause and we do need it.

This thing is driving me nuts

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources