1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Divisor is equal to zero error for group by expression

Discussion in 'SQL PL/SQL' started by pshuttleworth, Nov 29, 2013.

  1. pshuttleworth

    pshuttleworth Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi

    Im only a novice using pl/sql but an having problems witht he below scipt. Basically I have the issue where if it divides by zero i get an error. How do I resolve this?

    Thanks

    Paul

    select dw_mmgs.mmg5_id DEPT,
    dw_mmgs.mmg5_descr DEPT_NAME,
    sum(decode(sign(sales_item_val), 1, sales_item_val)) Gross_Sales_Value,
    sum(decode(sign(sales_item_val), -1, sales_item_val)) Refund_Value,
    sum(sales_item_val) Net_Sales,
    round(((sum(decode(sign(sales_item_val), -1, sales_item_val))/ sum(sales_item_val))*100),2) percent_of_Refunds
    from dss.dwt_sales_items, dss.dw_retl_prds, dss.dw_mmgs
    where dwt_sales_items.sales_audit_date between '&StartDate' and '&EndDate'
    and dwt_sales_items.loc_loc_id = '1097'
    and mmg2_id <> '0'
    and dw_retl_prds.rp_id = dwt_sales_items.rp_rp_id
    and dw_retl_prds.mmg_mmg_id_crnt = dw_mmgs.mmg6_id
    group by mmg5_id, mmg5_descr
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    HI

    Try with decode ( sum(sales_item_val),0,1,sum(sales_item_val)) .


    Regards
    Sambasiva Reddy.K
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  4. pshuttleworth

    pshuttleworth Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi

    Neither works im still getting the same error

    Thanks

    Paul
     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Paul,

    Can you tell me the value for SUM(sales_item_val)
     
  6. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi

    Have you tried with decode ( sum(sales_item_val),0,1,sum(sales_item_val)) like below query ?

    Code (SQL):
     SELECT   dw_mmgs.mmg5_id dept, dw_mmgs.mmg5_descr dept_name,
             SUM (DECODE (SIGN (sales_item_val), 1, sales_item_val)
                 ) gross_sales_value,
             SUM (DECODE (SIGN (sales_item_val), -1, sales_item_val))
                                                                     refund_value,
             SUM (sales_item_val) net_sales,
             ROUND ((  (  SUM (DECODE (SIGN (sales_item_val), -1, sales_item_val))
                        / DECODE(SUM (sales_item_val),0,1,SUM (sales_item_val))
                       )
                     * 100
                    ),
                    2
                   ) percent_of_refunds
        FROM dss.dwt_sales_items, dss.dw_retl_prds, dss.dw_mmgs
       WHERE dwt_sales_items.sales_audit_date BETWEEN '&StartDate' AND '&EndDate'
         AND dwt_sales_items.loc_loc_id = '1097'
         AND mmg2_id <> '0'
         AND dw_retl_prds.rp_id = dwt_sales_items.rp_rp_id
         AND dw_retl_prds.mmg_mmg_id_crnt = dw_mmgs.mmg6_id
    GROUP BY mmg5_id, mmg5_descr
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    He's not returning a NULL he's returning 0. NULLIF won't help here.
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    We really need create table statements for the tables involved in the query to test any code suggestions. In the absence of that DECODE may not be the answer as CASE is likely a better choice:

    Code (SQL):
    SQL> CREATE TABLE dw_mmgs(
      2          mmg2_id NUMBER,
      3          mmg2_descr varchar2(40),
      4          mmg5_id NUMBER,
      5          mmg5_descr varchar2(40),
      6          mmg6_id NUMBER,
      7          mmg6_descr varchar2(40));
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE dwt_sales_items(
      2          sales_audit_date DATE,
      3          loc_loc_id      NUMBER,
      4          rp_rp_id        NUMBER,
      5          sales_item_val  NUMBER);
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE dw_retl_prds(
      2          rp_id   NUMBER,
      3          mmg_mmg_id      NUMBER,
      4          mmg_mmg_id_crnt NUMBER);
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..100 loop
      3                  INSERT INTO dw_mmgs
      4                  VALUES(i, 'Desc'||i, i, 'Descr'||i, i, 'Descrip'||i);
      5                  INSERT INTO dwt_sales_items
      6                  VALUES(sysdate-i, 1097, MOD(i,7)+1, (MOD(i, 7))*1000);
      7                  INSERT INTO dw_retl_prds
      8                  VALUES(MOD(i, 7)+1, i, i);
      9          END loop;
     10
     11          commit;
     12
     13  END;
     14  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL>
    SQL> SELECT dw_mmgs.mmg5_id DEPT,
      2  dw_mmgs.mmg5_descr DEPT_NAME,
      3  SUM(decode(sign(sales_item_val), 1, sales_item_val)) Gross_Sales_Value,
      4  SUM(decode(sign(sales_item_val), -1, sales_item_val)) Refund_Value,
      5  SUM(sales_item_val) Net_Sales,
      6  round(((SUM(decode(sign(sales_item_val), -1, sales_item_val))/ CASE WHEN SUM(sales_item_val) = 0 THEN 1 ELSE SUM(sales_item_val) END)*100),2) percent_of_Refunds
      7  FROM dwt_sales_items, dw_retl_prds, dw_mmgs
      8  WHERE dwt_sales_items.sales_audit_date BETWEEN '&StartDate' AND '&EndDate'
      9  AND dwt_sales_items.loc_loc_id = '1097'
     10  AND mmg2_id <> '0'
     11  AND dw_retl_prds.rp_id = dwt_sales_items.rp_rp_id
     12  AND dw_retl_prds.mmg_mmg_id_crnt = dw_mmgs.mmg6_id
     13  GROUP BY mmg5_id, mmg5_descr;
    Enter VALUE FOR startdate: 10-JUL-13
    Enter VALUE FOR enddate: 11-NOV-13
    OLD   8: WHERE dwt_sales_items.sales_audit_date BETWEEN '&StartDate' AND '&EndDate'
    NEW   8: WHERE dwt_sales_items.sales_audit_date BETWEEN '10-JUL-13' AND '11-NOV-13'
     
          DEPT DEPT_NAME                                GROSS_SALES_VALUE REFUND_VALUE  NET_SALES PERCENT_OF_REFUNDS
    ---------- ---------------------------------------- ----------------- ------------ ---------- ------------------
            30 Descr30                                              24000                   24000
            34 Descr34                                              66000                   66000
            52 Descr52                                              33000                   33000
            55 Descr55                                              66000                   66000
            56 Descr56                                                                          0
            59 Descr59                                              33000                   33000
            61 Descr61                                              55000                   55000
            64 Descr64                                              11000                   11000
            67 Descr67                                              44000                   44000
            74 Descr74                                              44000                   44000
            90 Descr90                                              66000                   66000
     
          DEPT DEPT_NAME                                GROSS_SALES_VALUE REFUND_VALUE  NET_SALES PERCENT_OF_REFUNDS
    ---------- ---------------------------------------- ----------------- ------------ ---------- ------------------
             7 Descr7                                                                           0
            17 Descr17                                              33000                   33000
            23 Descr23                                              24000                   24000
            27 Descr27                                              66000                   66000
            28 Descr28                                                                          0
            35 Descr35                                                                          0
            44 Descr44                                              24000                   24000
            54 Descr54                                              55000                   55000
            91 Descr91                                                                          0
            95 Descr95                                              44000                   44000
            96 Descr96                                              55000                   55000
     
          DEPT DEPT_NAME                                GROSS_SALES_VALUE REFUND_VALUE  NET_SALES PERCENT_OF_REFUNDS
    ---------- ---------------------------------------- ----------------- ------------ ---------- ------------------
             2 Descr2                                               24000                   24000
             8 Descr8                                               11000                   11000
            19 Descr19                                              55000                   55000
            29 Descr29                                              11000                   11000
            37 Descr37                                              24000                   24000
            43 Descr43                                              11000                   11000
            51 Descr51                                              24000                   24000
            63 Descr63                                                                          0
            70 Descr70                                                                          0
            73 Descr73                                              33000                   33000
            87 Descr87                                              33000                   33000
     
          DEPT DEPT_NAME                                GROSS_SALES_VALUE REFUND_VALUE  NET_SALES PERCENT_OF_REFUNDS
    ---------- ---------------------------------------- ----------------- ------------ ---------- ------------------
            94 Descr94                                              33000                   33000
             6 Descr6                                               66000                   66000
            11 Descr11                                              44000                   44000
            18 Descr18                                              44000                   44000
            20 Descr20                                              66000                   66000
            31 Descr31                                              33000                   33000
            40 Descr40                                              55000                   55000
            45 Descr45                                              33000                   33000
            46 Descr46                                              44000                   44000
            60 Descr60                                              44000                   44000
            62 Descr62                                              66000                   66000
     
          DEPT DEPT_NAME                                GROSS_SALES_VALUE REFUND_VALUE  NET_SALES PERCENT_OF_REFUNDS
    ---------- ---------------------------------------- ----------------- ------------ ---------- ------------------
            66 Descr66                                              33000                   33000
            69 Descr69                                              66000                   66000
            78 Descr78                                              11000                   11000
            89 Descr89                                              55000                   55000
            93 Descr93                                              24000                   24000
            97 Descr97                                              66000                   66000
             1 Descr1                                               11000                   11000
            10 Descr10                                              33000                   33000
            13 Descr13                                              66000                   66000
            26 Descr26                                              55000                   55000
            32 Descr32                                              44000                   44000
     
          DEPT DEPT_NAME                                GROSS_SALES_VALUE REFUND_VALUE  NET_SALES PERCENT_OF_REFUNDS
    ---------- ---------------------------------------- ----------------- ------------ ---------- ------------------
            39 Descr39                                              44000                   44000
            49 Descr49                                                                          0
            53 Descr53                                              44000                   44000
            71 Descr71                                              11000                   11000
            79 Descr79                                              24000                   24000
            81 Descr81                                              44000                   44000
            82 Descr82                                              55000                   55000
            86 Descr86                                              24000                   24000
            92 Descr92                                              11000                   11000
            99 Descr99                                              11000                   11000
             3 Descr3                                               33000                   33000
     
          DEPT DEPT_NAME                                GROSS_SALES_VALUE REFUND_VALUE  NET_SALES PERCENT_OF_REFUNDS
    ---------- ---------------------------------------- ----------------- ------------ ---------- ------------------
             4 Descr4                                               44000                   44000
             5 Descr5                                               55000                   55000
            15 Descr15                                              11000                   11000
            25 Descr25                                              44000                   44000
            36 Descr36                                              11000                   11000
            72 Descr72                                              24000                   24000
            77 Descr77                                                                          0
            98 Descr98                                                                          0
            14 Descr14                                                                          0
            16 Descr16                                              24000                   24000
            21 Descr21                                                                          0
     
          DEPT DEPT_NAME                                GROSS_SALES_VALUE REFUND_VALUE  NET_SALES PERCENT_OF_REFUNDS
    ---------- ---------------------------------------- ----------------- ------------ ---------- ------------------
            22 Descr22                                              11000                   11000
            24 Descr24                                              33000                   33000
            41 Descr41                                              66000                   66000
            47 Descr47                                              55000                   55000
            58 Descr58                                              24000                   24000
            68 Descr68                                              55000                   55000
            76 Descr76                                              66000                   66000
            83 Descr83                                              66000                   66000
             9 Descr9                                               24000                   24000
            12 Descr12                                              55000                   55000
            33 Descr33                                              55000                   55000
     
          DEPT DEPT_NAME                                GROSS_SALES_VALUE REFUND_VALUE  NET_SALES PERCENT_OF_REFUNDS
    ---------- ---------------------------------------- ----------------- ------------ ---------- ------------------
            38 Descr38                                              33000                   33000
            42 Descr42                                                                          0
            48 Descr48                                              66000                   66000
            50 Descr50                                              11000                   11000
            57 Descr57                                              11000                   11000
            65 Descr65                                              24000                   24000
            75 Descr75                                              55000                   55000
            80 Descr80                                              33000                   33000
            84 Descr84                                                                          0
            85 Descr85                                              11000                   11000
            88 Descr88                                              44000                   44000
     
          DEPT DEPT_NAME                                GROSS_SALES_VALUE REFUND_VALUE  NET_SALES PERCENT_OF_REFUNDS
    ---------- ---------------------------------------- ----------------- ------------ ---------- ------------------
           100 Descr100                                             24000                   24000
     
    100 ROWS selected.
     
    SQL>
     
    Notice that when the sum of sales_item_val is 0 you still get a valid result and no division by zero error.
     
  9. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Here I am providing examples..

    select ename,sal,comm,sal/decode(comm,0,1,comm) from scott.emp;

    select ename,sal,comm,sal/case when comm=0 then 1 else comm end from scott.emp;

    In this scenario DECODE and CASE both are correct.

    In his script sum(sales_item_val) returns zero so error coming. So by using decode or case we just replacing 1 if it is zero.