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

[premamythili]

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

[Sikkandar.S.P]

 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.

[premamythili]

Thank u very much..