Home » SQL & PL/SQL » SQL & PL/SQL » Why difference between count(*) and count(*)
Why difference between count(*) and count(*) [message #660709] Sat, 25 February 2017 01:44 Go to next message
asliyanage
Messages: 60
Registered: January 2017
Member
I have employee table which contain employee details. When i tried with below it gives me different couts

1) select count(*) emp_count,
count(department_id) nut_null_dept_id
from employees;
e is difference for those values ?
i am getting emp_count=107 and nut_null_dept_id=106 . why there?

Re: Why difference between count(*) and count(*) [message #660710 is a reply to message #660709] Sat, 25 February 2017 01:50 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
select * from hr.employees where department_id is null;
Re: Why difference between count(*) and count(*) [message #660711 is a reply to message #660710] Sat, 25 February 2017 01:52 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
so why null value does not count in count(department_id) ?
Re: Why difference between count(*) and count(*) [message #660712 is a reply to message #660711] Sat, 25 February 2017 01:53 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
It is difficult to count something that does not exist.
Re: Why difference between count(*) and count(*) [message #660713 is a reply to message #660711] Sat, 25 February 2017 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Because this is how the function is defined in SQL standard.

Re: Why difference between count(*) and count(*) [message #660714 is a reply to message #660712] Sat, 25 February 2017 01:54 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
ok. That means null values are not count in group functions.
Re: Why difference between count(*) and count(*) [message #660715 is a reply to message #660714] Sat, 25 February 2017 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL Reference, Aggregate Functions, 7th paragraph (but read the whole page).

Re: Why difference between count(*) and count(*) [message #660716 is a reply to message #660715] Sat, 25 February 2017 02:00 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
ok. Thanks John Watson and Michel Cadot for quick response
Re: Why difference between count(*) and count(*) [message #660717 is a reply to message #660716] Sat, 25 February 2017 03:42 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
anyway i found that except "count" all the other group functions ignore the null values. is this correct?
Re: Why difference between count(*) and count(*) [message #660718 is a reply to message #660717] Sat, 25 February 2017 03:50 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
asliyanage wrote on Sat, 25 February 2017 09:42
anyway i found that except "count" all the other group functions ignore the null values. is this correct?
No. count(something) ignores null values. You have already proved this.
Re: Why difference between count(*) and count(*) [message #660719 is a reply to message #660718] Sat, 25 February 2017 04:45 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
yes count something ignores null value , but except count(*) ,all the other group functions ignore the null values.
Re: Why difference between count(*) and count(*) [message #660723 is a reply to message #660719] Sat, 25 February 2017 07:03 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
asliyanage wrote on Sat, 25 February 2017 04:45
yes count something ignores null value , but except count(*) ,all the other group functions ignore the null values.
Describe it how you will, make of it what you will, but nothing beats a 'proof test':

SQL> create table demo_tbl (fname varchar2(10),
  2                         lname varchar2(10)
  3                        )
  4  ;

Table created.

SQL> insert into demo_tbl values (null,null);

1 row created.

SQL> select count(*) from demo_tbl;

  COUNT(*)
----------
         1

1 row selected.

SQL> select count(fname) from demo_tbl;

COUNT(FNAME)
------------
           0

1 row selected.

SQL> insert into demo_tbl values ('Ed',null);

1 row created.

SQL> select count(*) from demo_tbl;

  COUNT(*)
----------
         2
1 row selected.

SQL> select count(fname) from demo_tbl;

COUNT(FNAME)
------------
           1

1 row selected.


Re: Why difference between count(*) and count(*) [message #660724 is a reply to message #660723] Sat, 25 February 2017 12:17 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
An "anomaly" (in "quotes" because as a relational engineer who believes in the SQL standard, I do not think it is an anomaly) that can cause confusion is the way nulls are treated by aggregations. Some developers find this confusing:
12.2> with t as (select 1 x from dual union all select null x from dual)
  2  select avg(x) from t;

    AVG(X)
----------
         1

12.2>
12.2>
12.2>
12.2> with t as (select 1 x from dual union all select null x from dual)
  2  select sum(x)/count(*) from t;

SUM(X)/COUNT(*)
---------------
             .5

12.2>
Re: Why difference between count(*) and count(*) [message #660725 is a reply to message #660717] Sat, 25 February 2017 13:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
asliyanage wrote on Sat, 25 February 2017 10:42
anyway i found that except "count" all the other group functions ignore the null values. is this correct?
Did you actually read the link and paragraph I pointed you to?

Re: Why difference between count(*) and count(*) [message #660732 is a reply to message #660723] Sun, 26 February 2017 01:07 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
still i am researching, once found will reply.
Re: Why difference between count(*) and count(*) [message #660741 is a reply to message #660732] Sun, 26 February 2017 01:40 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You are still researching what?
You have the standard, you have examples here, what still have to be researched?

Previous Topic: How to find No of Entries per week For consecutive week
Next Topic: Return all rows if no matching condition found
Goto Forum:
  


Current Time: Thu Mar 28 12:53:21 CDT 2024