Results 1 to 10 of 10
Thread: Calculations using SQL

041208, 14:51 #1Registered 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

041208, 15:09 #2Registered 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; 041208 at 15:17.
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development

041208, 23:04 #3Registered 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

041308, 15:14 #4Registered User
 Join Date
 Sep 2004
 Location
 Belgium
 Posts
 1,126
Originally Posted by dbshivaCode: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
_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/

041308, 16:22 #5Registered 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

041308, 22:04 #6Registered User
 Join Date
 Sep 2004
 Location
 Belgium
 Posts
 1,126
Originally Posted by dbshiva
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
Code:SELECT SUM(amt)/SUM(CASE amt WHEN 0 THEN 0 ELSE 1 END)) FROM a
_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/

041408, 13:49 #7Registered User
 Join Date
 Apr 2008
 Posts
 5
These column fields not rows
Thanks for the reply.
These are column fileds not rows.
Thanks
Shiva

041608, 17:09 #8Registered User
 Join Date
 Mar 2004
 Location
 Belarus
 Posts
 5
hi!
don't forget about division by zero

041708, 04:10 #9Registered User
 Join Date
 Sep 2004
 Location
 Belgium
 Posts
 1,126
Originally Posted by andrew348Code: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
_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/

041908, 17:17 #10Registered 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.