# Thread: Calculations using SQL

1. Registered User
Join Date
Apr 2008
Posts
5

## Unanswered: Calculations using SQL

I want to some calculations on fields of one table and then insert the results in to other table. Please let me know the sql statement for this.
For Ex:

Table A

actno amt1 amt2 amt 3 amt4 amt5 amt6 amt7 rent1 rent2 rent3

122 10 11 23 23 34 0 0 23 12 11 (one row for ex)

------------ so on ------------------------------rows

---------------------------------------rows ---------------

Table B:

mmactno RealAmt Avgamt

Here
Below are the calculations

B.mmactno= A.actno

B.RealAmt= (amt1+amt2+amt3....amt7)/(number non zero in values in( amt1 .......amt7) for Ex now it is 5 as amt6 and amt 7 are zero)

Avgamt=(amt1+amt2+amt3....amt7)/(total num of amts + totalnumber rents)

So like I have many calculations.
I am planning use sql Insert statement for this like

INSERT INTO B ( mmactno, RealAmt , Avgamt )
SELETCT
actno ,
( some sql calculation here),
(again sql calculation here)

from A

Please let know exact implementation and efficiant way of doing this.

Thanks
Shiva

2. Registered User
Join Date
Jan 2007
Location
Jena, Germany
Posts
2,721
You are very unspecific on how the expression "total num of amts + totalnumber rents" is to be determined. Is it per row or over the whole table? What compreses "amts" and what "rents"? Do you want to sum up all amts/rents or just count how many are not NULL? If that is known, you only need very basic SQL like this:
Code:
```INSERT INTO b(...)
SELECT actno,
amt1 + amt2 + amt3 + ...,
( amt1 + amt2 + ... ) / ( SELECT SUM(amt1 + amt2 + ... ) + SUM(rent1 + rent2 + ...)
FROM ...```
Last edited by stolze; 04-12-08 at 15:17.

3. Registered User
Join Date
Apr 2008
Posts
5

## Thanks for reply. Let me put it simple

Sorry for not putting clearly.

I have modified to calculations to explain my task

B.RealAmt= sum of (amt1 ,amt2,amt3....amt7) devided by (number non zero in values in( amt1 .......amt7) for Ex now it is 5 as amt6 and amt 7 are zero)

Here First I need find how many number of non zero values amonng (amt1, amt2 .....am7)
Avgamt= sum of (amt1,amt2,amt3....amt7)/(number of non zero vaule among rent1, rent2 rent3)
Here First I need find how many number of non zero values amonng (rent1, rent2 rent3)

I am not suppose to user sql procedure here.

Please let me know if this is clear.

Thanks
Shiva

4. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
Originally Posted by dbshiva
First I need find how many number of non zero values among (amt1, amt2 .....am7)
The simplest way to do this is as follows:
Code:
```SELECT
CASE amt1 WHEN 0 THEN 0 ELSE 1 END
+ CASE amt2 WHEN 0 THEN 0 ELSE 1 END
+ CASE amt3 WHEN 0 THEN 0 ELSE 1 END
+ CASE amt4 WHEN 0 THEN 0 ELSE 1 END
+ CASE amt5 WHEN 0 THEN 0 ELSE 1 END
+ CASE amt6 WHEN 0 THEN 0 ELSE 1 END
+ CASE amt7 WHEN 0 THEN 0 ELSE 1 END```
(assuming none of these are NULL, just zero).

5. Registered User
Join Date
Apr 2008
Posts
5

## Thanks

Thanks for the reply.
I am planning write this way. I do not know wether this work , I have to try

INSERT INTO B ( mmactno, RealAmt , Avgamt )
SELETCT
actno ,
( ( amt1 + amt2 + amt3 + amt4 +amt5 + amt5 + amt6 + amt7 + amt 8 ) / ( CASE amt1 WHEN 0 THEN 0 ELSE 1 END
+ CASE amt2 WHEN 0 THEN 0 ELSE 1 END
+ CASE amt3 WHEN 0 THEN 0 ELSE 1 END
+ CASE amt4 WHEN 0 THEN 0 ELSE 1 END
+ CASE amt5 WHEN 0 THEN 0 ELSE 1 END
+ CASE amt6 WHEN 0 THEN 0 ELSE 1 END
+ CASE amt7 WHEN 0 THEN 0 ELSE 1 END ) )
,
( ( amt1 + amt2 + amt3 + amt4 +amt5 + amt5 + amt6 + amt7 + amt 8 ) / ( CASE rent1 WHEN 0 THEN 0 ELSE 1 END
+ CASE rent2 WHEN 0 THEN 0 ELSE 1 END
+ CASE rent3WHEN 0 THEN 0 ELSE 1 END
) )

from A

Please let me know if this is effieiant way.

Thanks
Shivanand

6. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
Originally Posted by dbshiva
Please let me know if this is efficient way.
Yes, looks OK to me.

If the seven fields to be averaged would have been different rows instead of columns, the following would have been a simpler implementation:
Code:
```SELECT AVG(NULLIF(amt, 0))
FROM A```
for the equivalent
Code:
```SELECT SUM(amt)/SUM(CASE amt WHEN 0 THEN 0 ELSE 1 END))
FROM a```

7. Registered User
Join Date
Apr 2008
Posts
5

## These column fields not rows

Thanks for the reply.
These are column fileds not rows.
Thanks
Shiva

8. Registered User
Join Date
Mar 2004
Location
Belarus
Posts
5
hi!

don't forget about division by zero

9. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
Originally Posted by andrew348
don't forget about division by zero
OK, here goes:
Code:
```INSERT INTO B ( mmactno, RealAmt , Avgamt )
SELECT actno ,
( amt1 + amt2 + amt3 + amt4 +amt5 + amt6 + amt7 + amt8 )
/ NULLIF(CASE amt1 WHEN 0 THEN 0 ELSE 1 END
+ CASE amt2 WHEN 0 THEN 0 ELSE 1 END
+ CASE amt3 WHEN 0 THEN 0 ELSE 1 END
+ CASE amt4 WHEN 0 THEN 0 ELSE 1 END
+ CASE amt5 WHEN 0 THEN 0 ELSE 1 END
+ CASE amt6 WHEN 0 THEN 0 ELSE 1 END
+ CASE amt7 WHEN 0 THEN 0 ELSE 1 END
+ CASE amt8 WHEN 0 THEN 0 ELSE 1 END, 0) ,
( rent1 + rent2 + rent3 )
/ NULLIF(CASE rent1 WHEN 0 THEN 0 ELSE 1 END
+ CASE rent2 WHEN 0 THEN 0 ELSE 1 END
+ CASE rent3 WHEN 0 THEN 0 ELSE 1 END, 0)
FROM A```
So, when a denominator would be 0, it's turned into NULL, hence the division becomes NULL, meaning "undefined". (Could have done this for the nominator instead, in this case (assuming values cannot be negative), but doing it in the denominator is more "generic", and would be the only correct way if also negative values are possible.)

10. Registered User
Join Date
Mar 2004
Location
Belarus
Posts
5
Exactly,
the last question: is table B accept NULLs in RealAmt and Avgamt columns
but seems it doesn't matter now

Regards.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•