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!

Show value as ZEro from a Group function in case now row is returned

Discussion in 'SQL PL/SQL' started by sharo, Jul 19, 2011.

  1. sharo

    sharo Active Member

    Messages:
    6
    Likes Received:
    1
    Trophy Points:
    65
    Hello,

    I want to show the result as zero from a group Function if there are no rows returned. Example:

    Code (SQL):
    SELECT
    SUM(CASE period  WHEN 201001 THEN  SUM(f.sales) END) AS ITEM_201001,
    SUM(CASE period  WHEN 201002 THEN  SUM(f.sales) END) AS ITEM_201002,
    SUM(CASE period  WHEN 201003 THEN  SUM(f.sales) END) AS ITEM_201003,
    FROM SALES S, PERIOD P
    WHERE P.Period_id IN (201001,201002,210103)
    AND P.Period_id =S.Period_id
     
    Now if there is no row for "201003", i will get the result as Null(blank). Is it possible to show a zero instead.
    I tried NVL, COLEASCE but it does not work as for these we need a row atleast with Null Value.


    Thanks,
    Sharo
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Code (SQL):

    14:00:27 sql> SELECT  e.deptno , SUM(sal) sal
    14:00:35   2  FROM  emp e
    14:00:42   3  GROUP BY e.deptno;

        deptno        sal
    ---------- ----------
            30       9400
            20      10875
            10       8750

    14:00:47 sql> SELECT d.dpt, nvl(SUM(sal),0)
    14:00:59   2  FROM emp e ,
    14:01:02   3       (SELECT 10 dpt FROM dual  UNION
    14:01:06   4        SELECT 20 dpt FROM dual  UNION
    14:01:10   5        SELECT 30 dpt FROM dual  UNION
    14:01:13   6        SELECT 40 dpt FROM dual  UNION
    14:01:17   7        SELECT 50 dpt FROM dual   ) d
    14:01:20   8  WHERE e.deptno  (+)= d.dpt
    14:01:32   9  GROUP BY d.dpt;

           dpt nvl(SUM(sal),0)
    ---------- ---------------
            30            9400
            20           10875
            50               0
            40               0
            10            8750

    14:01:34 sql>