4 Replies Latest reply: Oct 8, 2013 2:37 PM by Frank Kulash RSS

    Is there a way to return 0 if count is null?

    Dev. Musbah

      Hi All,

      Suppose I have this query in a report:

      Select count(emp_class)
      from employees
      where emp_class = 1
      group by emp_class
      
      

      Is there a way to make it returning the value of 0 if it returns null?

      I tried this: Select nvl(count(emp_class, 0) but it doesn't work!

       

      Note: I'm using Oracle DB 10g, Reports 6i

      Thank you

        • 1. Re: Is there a way to return 0 if count is null?
          Frank Kulash

          Hi,

           

          If you want the result set to contain 1 row, regardless of what (if anything) is in the table, then don't use a GROUP BY clause.  Since you're only displaying 1 value of emp_class, you don't need the GROUP BY anyway

           

          Select    count (emp_class)     as cnt

          from      employees 

          where     emp_class  = 1 
          ;

          • 2. Re: Is there a way to return 0 if count is null?
            Hoek

            Maybe:

             

             

            SQL>  select count(*) from dual where 1=2;

             

              COUNT(*)

            ----------

                     0

             

            1 row selected.

             

            SQL> select count(*) from dual where 1=2 group by dummy;

             

            no rows selected

             

            SQL> select count(*) from dual where 1=2 group by dummy union select 0 from dual;

             

              COUNT(*)

            ----------

                     0

             

            1 row selected.

            • 3. Re: Is there a way to return 0 if count is null?
              Dev. Musbah

              No if the count returns values (suppose emp_class = 2) the result set will contain 4 values!

              I want (emp_class = 1) returns 0 if it returns null

              • 4. Re: Is there a way to return 0 if count is null?
                Frank Kulash

                Hi,

                 

                Dev.Musbah wrote:

                 

                No if the count returns values (suppose emp_class = 2) the result set will contain 4 values!

                I want (emp_class = 1) returns 0 if it returns null

                Sorry, I don't understand you.

                Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.

                Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.

                Always say which version of Oracle you're using (for example, 11.2.0.2.0).

                See the forum FAQ: https://forums.oracle.com/message/9362002

                 

                Are you interested in emp_class=1 or emp_class=2?  If you're interested in emp_class=1, then include "emp_class=1" in the WHERE clause, like you originally did.  It won't matter how many rows (if any) have emp_class=2.

                If you're interested in emp_class=2, then say "emp_class = 2" in the WHERE clause.

                 

                Also, COUNT never returns NULL.  It always returns a non-negative integer.