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!

what will be the return value of COUNT(*) if no records were selected by the group by

Discussion in 'General' started by premamythili, Nov 24, 2009.

  1. premamythili

    premamythili Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi,
    i have a query like this
    select field1,count(*) from table1 where field1 in(5,3,7) group by field1

    i want zero to be displayed incase of no rows retrieved for the field1 values.

    field1 count(*)
    5 12
    3 15
    7 0

    can any of you plese help me on this?

    Thanks
     
  2. Sikkandar.S.P

    Sikkandar.S.P Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Chennai
    Re: what will be the return value of COUNT(*) if no records were selected by the grou

    Code (Text):


    SQL> SELECT FIELD1,COUNT(*) FROM TABLE1
      2  GROUP BY FIELD1
      3  ORDER BY 1
      4  /

        FIELD1   COUNT(*)
    ---------- ----------
             3         15
             5         12

    2 rows selected.

    SQL> SELECT   FIELD2,
      2           COUNT(FIELD1)
      3  FROM     TABLE1 A
      4           FULL OUTER JOIN (SELECT 3 FIELD2
      5                            FROM   DUAL
      6                            UNION ALL
      7                            SELECT 5
      8                            FROM   DUAL
      9                            UNION ALL
     10                            SELECT 7
     11                            FROM   DUAL) B
     12             ON A.FIELD1 = B.FIELD2
     13  GROUP BY FIELD2
     14  ORDER BY 1
     15  /

        FIELD2 COUNT(FIELD1)
    ---------- -------------
             3            15
             5            12
             7             0

    3 rows selected.
     
     
  3. premamythili

    premamythili Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Re: what will be the return value of COUNT(*) if no records were selected by the grou

    Thank u very much..