# Computing percentage change between tables

## Recommended Posts

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

##### Share on other sites

You should do a little research on joins. You didn't give us enough info. Is there an exact 1:1 relationship between the accounts in the two tables or are their missing entries from one or both tables? And percentage of what, the percentage of temp1.balance/temp2.balance will be different than temp2.balance/temp1.balance.

Also at the schema level, you should use a precise data type (numeric) for the balance instead of float.

This makes some assumptions that may be incorrect but it should get you close:

```SELECT temp1.account_no, temp1.balance,
CASE WHEN temp1.balance = temp2.balance THEN 0.0
ELSE ABS(temp1.balance = temp2.balance) / GREATEST(temp1.balance, temp2.balance)
END
FROM temp1
JOIN temp2 ON temp1.account_no = temp2.account_no
```

##### Share on other sites

Hi,

Thanks a lot for the code.. its very neatly gets rid of my zero divisor problem. I have one question though: Like you pointed out there is no 1:1 relationship. I should've mentioned it earlier in my post.

For all account numbers in temp2 ( which has lesser records than temp1 ( the master record )), i want to calculate the balance differences in temp2 and temp1 and output to a new table.

##### Share on other sites

I'd recommend against outputting to another table. Create a view instead. (Unless you are talking hundreds of millions of accounts, in which case you'd probably be making 6 figures and answering my questions)

It sounds like all records in temp2 would have a match in temp1 so the query will be fine. Otherwise, you'd have to use a left outer join. You may also need to account for an edge case where perhaps they sent in too much money and their balance is negative in one table and 0 in another. That would still cause a div by 0 error.

##### Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.