Computing percentage change from two tables

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

General SQL Server Forums

New to SQL Server Programming

Computing percentage change from two tables

Author

Topic

novice82
Starting Member

5 Posts

Posted - 2009-07-20 : 04:09:21

Hi,

I'm a novice to sql scripting. Am trying to figure out a design problem, involving some arithmatic computation.

I have two tables temp1 and temp2 with two feilds
account no., ( Common in both tables )
balance ( float data type ).

1. I want to compare the balance feilds in temp1 and temp2.
print out the no. of accounts and percentage of match and mismatch.
2. output the a/c nos. whose balances dont match into a seperate table..

another question, that I have is, how does sql handle the computation, if a value in a particular field is divided by 0 ?

any help, will get me started.

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-20 : 05:53:55

[code]
Try this

select t1.acc_no,
100.0*sum(case when t2.balance is not null then 1 else 0 end)/count(t1.acc_no) as matches,
100.0*sum(case when t2.balance is null then 1 else 0 end)/count(t1.acc_no) as no_matches
from
temp1 as t1 left join temp2 as t2
on t1.acc_no=t2.acc_no
group by
t1.acc_no

[/code]

<<
another question, that I have is, how does sql handle the computation, if a value in a particular field is divided by 0 ?
>>

It will give you the error

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Madhivanan

Failing to plan is Planning to fail

novice82
Starting Member

5 Posts

Posted - 2009-07-20 : 06:28:23

Hi Madhivanan,

Thanks for your help.. I get an error when i run the following query at the line: < Sql command Not properly ended >
temp1 as t1 left join temp2 as t2

any suggestions ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-20 : 08:24:04

Are you using SQL Server?

Madhivanan

Failing to plan is Planning to fail

novice82
Starting Member

5 Posts

Posted - 2009-07-20 : 21:17:05

Hi!

No. I'm using Oracle 10g

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 21:20:36

quote:
Originally posted by novice82

Hi!

No. I'm using Oracle 10g

For your information, this is a Microsoft SQL Server forum. You have posted in "New to SQL Server Programming". For oracle question, try orafaq.com or dbforums.com

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources