Results 1 to 10 of 10
  1. #1
    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. #2
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    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. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote 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).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    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. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote 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
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Apr 2008
    Posts
    5

    These column fields not rows

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

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

    don't forget about division by zero

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote 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.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    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
  •