Home » SQL & PL/SQL » SQL & PL/SQL » Avoid divisions by zero
Avoid divisions by zero [message #578211] Mon, 25 February 2013 12:18 Go to next message
RMSoares
Messages: 46
Registered: September 2010
Location: Lisboa
Member

Hello,
I have a query that performs the sum of a field and divides by the number of occurrences, got me a problem because if there are no occurrences would cause a divide by zero error in Query.

The solution I found might not have been the best, but at the time was possible.

I put a Case, when the sum of the number of occurrence is zero then return zero, otherwise performs the division of the sum of the values by the number of occurrences.


SELECT CLIENT_ID, 
    TO_NUMBER(TO_CHAR(DTPROC, ''YYYYMM'')), 
    DTPROC, EXCHANG_VAL, 
    CASE WHEN SUM(NVL(NUM_MONTH_ACCOUNT,0)) = 0 THEN 0 ELSE SUM(VAL_MONTH_ACCOUNT)/SUM(NUM_MONTH_ACCOUNT) END as VAL_BALLANC_AVERAG_ACCOUNT, 
    CASE WHEN SUM(NVL(NUM_ACM_ACCOUNT,0)) = 0 THEN 0 ELSE (SUM(VAL_ACM_ACCOUNT) + SUM(VAL_ACM_MORTG) - SUM(VAL_ULT_ACCOUNT)) / (SUM(NUM_ACM_ACCOUNT) - (CASE WHEN SUM(NUM_ACCOUNT) > 1 THEN 1 ELSE 0 END)) END  as VAL_BALLANC_FINACIAL, 
    SUM(NUM_ACCOUNT),
    SUM(VAL_LAST_ACCOUNT),
    SUM(VAL_ACM_ACCOUNT), 
    SUM(VAL_ACM_SCOR), 
    SUM(NUM_ACM_ACCOUNT), 
    SUM(VAL_MONTH_ACCOUNT), 
    SUM(NUM_MONTH_ACCOUNT) 
        FROM ODS_REF_BALLANCS_AGG_AUX 
            WHERE DTPROC =  TO_DATE(P_DAT, ''YYYYMMDD'') OR DTPROC = (TO_DATE(TO_CHAR(ADD_MONTHS(DTPROC, 1), ''YYYYMM'') || ''01'', ''YYYYMMDD'') -1)
                GROUP BY CLIENT_ID, TO_NUMBER(TO_CHAR(DTPROC, ''YYYYMM'')), DTPROC, EXCHANG_VAL 




Now this query is very slow, because have CASE,SUM and GROUP BY, so I need to optimize it.


Someone have an idea to do this validation (if the divisor is zero) more performance?


thanks in advance
Re: Avoid divisions by zero [message #578212 is a reply to message #578211] Mon, 25 February 2013 12:40 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

And feedback to (and thank) those that help you, I'm waiting for you on your latest topicS.

Regards
Michel

[Updated on: Mon, 25 February 2013 12:41]

Report message to a moderator

Re: Avoid divisions by zero [message #578301 is a reply to message #578212] Tue, 26 February 2013 13:02 Go to previous messageGo to next message
RMSoares
Messages: 46
Registered: September 2010
Location: Lisboa
Member

The example included in annex describes the query I want to execute, in some cases the NUM_MONTH_ACCOUNT and/or NUM_ACM_ACCOUNT are zero produces an error in division (such as can be seen with the execution of this query)
with
     data as (     
        SELECT 100110 CLIENT_ID, TO_DATE('20130225', 'YYYYMMDD') as DTPROC, 100 as EXCHANG_VAL, 
        1 as NUM_ACCOUNT, 1 as NUM_MONTH_ACCOUNT, 11 as NUM_ACM_ACCOUNT,
        500 as VAL_MONTH_ACCOUNT, 6000 as VAL_ACM_ACCOUNT, 100 as VAL_ULT_ACCOUNT, 100 as VAL_LAST_ACCOUNT,
        200 as VAL_ACM_MORTG, 100 as VAL_ACM_SCOR 
        FROM DUAL
        UNION
        SELECT 100110 CLIENT_ID, TO_DATE('20130225', 'YYYYMMDD') as DTPROC, 400 as EXCHANG_VAL, 
        3 as NUM_ACCOUNT, 3 as NUM_MONTH_ACCOUNT, 2 as NUM_ACM_ACCOUNT,
        350 as VAL_MONTH_ACCOUNT, 9000 as VAL_ACM_ACCOUNT, 200 as VAL_ULT_ACCOUNT, 100 as VAL_LAST_ACCOUNT,
        40 as VAL_ACM_MORTG, 20 as VAL_ACM_SCOR 
        FROM DUAL
        UNION
        SELECT 100110 CLIENT_ID, TO_DATE('20130225', 'YYYYMMDD') as DTPROC, 400 as EXCHANG_VAL, 
        4 as NUM_ACCOUNT, 4 as NUM_MONTH_ACCOUNT, 2 as NUM_ACM_ACCOUNT,
        400 as VAL_MONTH_ACCOUNT, 200 as VAL_ACM_ACCOUNT, 100 as VAL_ULT_ACCOUNT, 50 as VAL_LAST_ACCOUNT,
        40 as VAL_ACM_MORTG, 20 as VAL_ACM_SCOR 
        FROM DUAL           
        UNION
        SELECT 185420 CLIENT_ID, TO_DATE('20130225', 'YYYYMMDD') as DTPROC, 500 as EXCHANG_VAL, 
        0 as NUM_ACCOUNT, 0 as NUM_MONTH_ACCOUNT, 10 as NUM_ACM_ACCOUNT,
       0 as VAL_MONTH_ACCOUNT, 100 as VAL_ACM_ACCOUNT, 0 as VAL_ULT_ACCOUNT, 0 as VAL_LAST_ACCOUNT,
        40 as VAL_ACM_MORTG, 20 as VAL_ACM_SCOR 
        FROM DUAL        
     )
SELECT CLIENT_ID, 
    TO_NUMBER(TO_CHAR(DTPROC, 'YYYYMM')), 
    DTPROC, EXCHANG_VAL, 
    SUM(VAL_MONTH_ACCOUNT)/SUM(NUM_MONTH_ACCOUNT), 
    (SUM(VAL_ACM_ACCOUNT) + SUM(VAL_ACM_MORTG) - SUM(VAL_ULT_ACCOUNT)) / (SUM(NUM_ACM_ACCOUNT) - (CASE WHEN SUM(NUM_ACCOUNT) > 1 THEN 1 ELSE 0 END)), 
    SUM(NUM_ACCOUNT),
    SUM(VAL_LAST_ACCOUNT),
    SUM(VAL_ACM_ACCOUNT), 
    SUM(VAL_ACM_SCOR), 
    SUM(NUM_ACM_ACCOUNT), 
    SUM(VAL_MONTH_ACCOUNT), 
    SUM(NUM_MONTH_ACCOUNT) 
        FROM data
            WHERE DTPROC =  TO_DATE('20130225', 'YYYYMMDD') OR DTPROC = (TO_DATE(TO_CHAR(ADD_MONTHS(DTPROC, 1), 'YYYYMM') || '01', 'YYYYMMDD') -1)
                GROUP BY CLIENT_ID, TO_NUMBER(TO_CHAR(DTPROC, 'YYYYMM')), DTPROC, EXCHANG_VAL 



To overcome this error I had used the CASE before each sum, if the divisor is zero then the result is zero, otherwise execute the query directly.
with
     data as (     
        SELECT 100110 CLIENT_ID, TO_DATE('20130225', 'YYYYMMDD') as DTPROC, 100 as EXCHANG_VAL, 
        1 as NUM_ACCOUNT, 1 as NUM_MONTH_ACCOUNT, 11 as NUM_ACM_ACCOUNT,
        500 as VAL_MONTH_ACCOUNT, 6000 as VAL_ACM_ACCOUNT, 100 as VAL_ULT_ACCOUNT, 100 as VAL_LAST_ACCOUNT,
        200 as VAL_ACM_MORTG, 100 as VAL_ACM_SCOR 
        FROM DUAL
        UNION
        SELECT 100110 CLIENT_ID, TO_DATE('20130225', 'YYYYMMDD') as DTPROC, 400 as EXCHANG_VAL, 
        3 as NUM_ACCOUNT, 3 as NUM_MONTH_ACCOUNT, 2 as NUM_ACM_ACCOUNT,
        350 as VAL_MONTH_ACCOUNT, 9000 as VAL_ACM_ACCOUNT, 200 as VAL_ULT_ACCOUNT, 100 as VAL_LAST_ACCOUNT,
        40 as VAL_ACM_MORTG, 20 as VAL_ACM_SCOR 
        FROM DUAL
        UNION
        SELECT 100110 CLIENT_ID, TO_DATE('20130225', 'YYYYMMDD') as DTPROC, 400 as EXCHANG_VAL, 
        4 as NUM_ACCOUNT, 4 as NUM_MONTH_ACCOUNT, 2 as NUM_ACM_ACCOUNT,
        400 as VAL_MONTH_ACCOUNT, 200 as VAL_ACM_ACCOUNT, 100 as VAL_ULT_ACCOUNT, 50 as VAL_LAST_ACCOUNT,
        40 as VAL_ACM_MORTG, 20 as VAL_ACM_SCOR 
        FROM DUAL           
        UNION
        SELECT 185420 CLIENT_ID, TO_DATE('20130225', 'YYYYMMDD') as DTPROC, 500 as EXCHANG_VAL, 
        0 as NUM_ACCOUNT, 0 as NUM_MONTH_ACCOUNT, 10 as NUM_ACM_ACCOUNT,
       0 as VAL_MONTH_ACCOUNT, 100 as VAL_ACM_ACCOUNT, 0 as VAL_ULT_ACCOUNT, 0 as VAL_LAST_ACCOUNT,
        40 as VAL_ACM_MORTG, 20 as VAL_ACM_SCOR 
        FROM DUAL        
     )
SELECT CLIENT_ID, 
    TO_NUMBER(TO_CHAR(DTPROC, 'YYYYMM')), 
    DTPROC, EXCHANG_VAL, 
    CASE WHEN SUM(NVL(NUM_MONTH_ACCOUNT,0)) = 0 THEN 0 ELSE SUM(VAL_MONTH_ACCOUNT)/SUM(NUM_MONTH_ACCOUNT) END, 
    CASE WHEN SUM(NVL(NUM_ACM_ACCOUNT,0)) = 0 THEN 0 ELSE (SUM(VAL_ACM_ACCOUNT) + SUM(VAL_ACM_MORTG) - SUM(VAL_ULT_ACCOUNT)) / (SUM(NUM_ACM_ACCOUNT) - (CASE WHEN SUM(NUM_ACCOUNT) > 1 THEN 1 ELSE 0 END)) END, 
    SUM(NUM_ACCOUNT),
    SUM(VAL_LAST_ACCOUNT),
    SUM(VAL_ACM_ACCOUNT), 
    SUM(VAL_ACM_SCOR), 
    SUM(NUM_ACM_ACCOUNT), 
    SUM(VAL_MONTH_ACCOUNT), 
    SUM(NUM_MONTH_ACCOUNT) 
        FROM data
            WHERE DTPROC =  TO_DATE('20130225', 'YYYYMMDD') OR DTPROC = (TO_DATE(TO_CHAR(ADD_MONTHS(DTPROC, 1), 'YYYYMM') || '01', 'YYYYMMDD') -1)
                GROUP BY CLIENT_ID, TO_NUMBER(TO_CHAR(DTPROC, 'YYYYMM')), DTPROC, EXCHANG_VAL 


The solution may not have been the best but it was possible, however this solution causes a performance problem in executing the query.


The goal is to calculated the average of financial movements by customers, which I done the sum of all movements and divided by the number of movements (for each registration there may be more than one movement) in the case where no movement the average zero, but to know the total number of movements of the customer have to sum all the movements associated to the customer.

SUM(VAL_MONTH_ACCOUNT)/SUM(NUM_MONTH_ACCOUNT)


Is there any better way to make these cases?
Re: Avoid divisions by zero [message #578307 is a reply to message #578301] Tue, 26 February 2013 13:22 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


understand & realize that since we don't have your tables or data, we can't run, test, or tune any code you post here.
Re: Avoid divisions by zero [message #578310 is a reply to message #578301] Tue, 26 February 2013 15:09 Go to previous messageGo to next message
joy_division
Messages: 4528
Registered: February 2005
Location: East Coast USA
Senior Member
RMSoares wrote on Tue, 26 February 2013 14:02

To overcome this error I had used the CASE before each sum, if the divisor is zero then the result is zero, otherwise execute the query directly.
with
     data as (     
        SELECT 100110 CLIENT_ID, TO_DATE('20130225', 'YYYYMMDD') as DTPROC, 100 as EXCHANG_VAL, 
        1 as NUM_ACCOUNT, 1 as NUM_MONTH_ACCOUNT, 11 as NUM_ACM_ACCOUNT,
        500 as VAL_MONTH_ACCOUNT, 6000 as VAL_ACM_ACCOUNT, 100 as VAL_ULT_ACCOUNT, 100 as VAL_LAST_ACCOUNT,
        200 as VAL_ACM_MORTG, 100 as VAL_ACM_SCOR 
        FROM DUAL
        UNION
        SELECT 100110 CLIENT_ID, TO_DATE('20130225', 'YYYYMMDD') as DTPROC, 400 as EXCHANG_VAL, 
        3 as NUM_ACCOUNT, 3 as NUM_MONTH_ACCOUNT, 2 as NUM_ACM_ACCOUNT,
        350 as VAL_MONTH_ACCOUNT, 9000 as VAL_ACM_ACCOUNT, 200 as VAL_ULT_ACCOUNT, 100 as VAL_LAST_ACCOUNT,
        40 as VAL_ACM_MORTG, 20 as VAL_ACM_SCOR 
        FROM DUAL
        UNION
        SELECT 100110 CLIENT_ID, TO_DATE('20130225', 'YYYYMMDD') as DTPROC, 400 as EXCHANG_VAL, 
        4 as NUM_ACCOUNT, 4 as NUM_MONTH_ACCOUNT, 2 as NUM_ACM_ACCOUNT,
        400 as VAL_MONTH_ACCOUNT, 200 as VAL_ACM_ACCOUNT, 100 as VAL_ULT_ACCOUNT, 50 as VAL_LAST_ACCOUNT,
        40 as VAL_ACM_MORTG, 20 as VAL_ACM_SCOR 
        FROM DUAL           
        UNION
        SELECT 185420 CLIENT_ID, TO_DATE('20130225', 'YYYYMMDD') as DTPROC, 500 as EXCHANG_VAL, 
        0 as NUM_ACCOUNT, 0 as NUM_MONTH_ACCOUNT, 10 as NUM_ACM_ACCOUNT,
       0 as VAL_MONTH_ACCOUNT, 100 as VAL_ACM_ACCOUNT, 0 as VAL_ULT_ACCOUNT, 0 as VAL_LAST_ACCOUNT,
        40 as VAL_ACM_MORTG, 20 as VAL_ACM_SCOR 
        FROM DUAL        
     )
SELECT CLIENT_ID, 
    TO_NUMBER(TO_CHAR(DTPROC, 'YYYYMM')), 
    DTPROC, EXCHANG_VAL, 
    CASE WHEN SUM(NVL(NUM_MONTH_ACCOUNT,0)) = 0 THEN 0 ELSE SUM(VAL_MONTH_ACCOUNT)/SUM(NUM_MONTH_ACCOUNT) END, 
    CASE WHEN SUM(NVL(NUM_ACM_ACCOUNT,0)) = 0 THEN 0 ELSE (SUM(VAL_ACM_ACCOUNT) + SUM(VAL_ACM_MORTG) - SUM(VAL_ULT_ACCOUNT)) / (SUM(NUM_ACM_ACCOUNT) - (CASE WHEN SUM(NUM_ACCOUNT) > 1 THEN 1 ELSE 0 END)) END, 
    SUM(NUM_ACCOUNT),
    SUM(VAL_LAST_ACCOUNT),
    SUM(VAL_ACM_ACCOUNT), 
    SUM(VAL_ACM_SCOR), 
    SUM(NUM_ACM_ACCOUNT), 
    SUM(VAL_MONTH_ACCOUNT), 
    SUM(NUM_MONTH_ACCOUNT) 
        FROM data
            WHERE DTPROC =  TO_DATE('20130225', 'YYYYMMDD') OR DTPROC = (TO_DATE(TO_CHAR(ADD_MONTHS(DTPROC, 1), 'YYYYMM') || '01', 'YYYYMMDD') -1)
                GROUP BY CLIENT_ID, TO_NUMBER(TO_CHAR(DTPROC, 'YYYYMM')), DTPROC, EXCHANG_VAL 


The solution may not have been the best but it was possible, however this solution causes a performance problem in executing the query.

Is there any better way to make these cases?


What performance problem? When I run that query I get a result immediately.
Re: Avoid divisions by zero [message #578374 is a reply to message #578310] Wed, 27 February 2013 05:58 Go to previous message
RMSoares
Messages: 46
Registered: September 2010
Location: Lisboa
Member

Well the problem is not with 3the movements of one client, but 30 million customers daily

trying to simplify the question, the problem is in identifying the divisor is zero or not.

It is intended that the mean is calculated based on the total movements divided by the total number of movements.

In the example provide the client 1 has 2 records, with the 1st record with 1 movement and 2nd record with five movements. For the second there is only one record but for which have zero movement.

with
     data as (     
        SELECT 1 CLIENT_ID, 20130210 as DATPROC, 1 as NUM_ACCOUNT, 100 as VAL_ACCOUNT FROM DUAL 
        UNION
        SELECT 1 CLIENT_ID, 20130225 as DATPROC, 5 as NUM_ACCOUNT, 5900 as VAL_ACCOUNT FROM DUAL
        --
        UNION
        SELECT 2 CLIENT_ID, 20130226 as DATPROC, 0 as NUM_ACCOUNT, 0 as VAL_ACCOUNT FROM DUAL
     )
SELECT CLIENT_ID, 
            SUM(VAL_ACCOUNT)/(SUM(NUM_ACCOUNT)) as AVG_ACCOUNT
        FROM data
        GROUP BY CLIENT_ID


The Result is expected to:
- Client 1 = (100 + 5900) / (1 + 5)

- Client 2, since the sum of the number of movement is zero, the result must be zero. But in this situation there is an error in the query to divide by zero.

How can I test if the sum of the number of movement is equals to zero? in order to avoid division by zero.
Previous Topic: Calling other procedures from autonomous procedure
Next Topic: Sysdate between Two date columns
Goto Forum:
  


Current Time: Tue Oct 21 23:01:25 CDT 2014

Total time taken to generate the page: 0.11576 seconds