Home » SQL & PL/SQL » SQL & PL/SQL » count() function doubt
count() function doubt [message #206629] Thu, 30 November 2006 21:42 Go to next message
vp17in
Messages: 12
Registered: November 2006
Junior Member
test123 is the table which contains 4 columns. I had inserted duplicates as well as null values in the id column.

select count(*) from test123;
will return all the rows including null as well duplicates.

Here id is the first of the 4 columns available.

select count(id) from test123;
This will print the count after eliminate all the null values present.

select count(distinct id) from test123;
This will print the count after eliminate both null values as well as duplicates.

My doubt is
select count(1) from test123;

Does 1 mean the first column in the table.
This returns all the rows including null as well duplicates.
What does 1 signify?

Kindly explain

[Updated on: Thu, 30 November 2006 21:47]

Report message to a moderator

Re: count() function doubt [message #206632 is a reply to message #206629] Thu, 30 November 2006 22:31 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Check these.
select id from table;

select 1 from table;

The second one prints 1's as many number of rows you have in the table.
select count(1),count(0),count('A') from table;

Count will work as the same way you have mentioned.
As it is not selecting any null values, the count would be the number of rows in that table.

By
Vamsi
Re: count() function doubt [message #206639 is a reply to message #206629] Thu, 30 November 2006 23:07 Go to previous messageGo to next message
vp17in
Messages: 12
Registered: November 2006
Junior Member
Thanks Vamsi.

The code was like

select count(*) from test123 where user_id = input_user;

But later it was modified to

select count(1) from test123 where user_id = input_user;

did u notice the change, the count(*) was modified to count(1).

When I asked why, the answer was this will improve the performance.
I didn't understand how it is going to improve the performance.
Any idea?

Re: count() function doubt [message #206642 is a reply to message #206639] Thu, 30 November 2006 23:18 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You could have search the forum. Cool
Diff b/w count(1) and count(*)

By
Vamsi
Re: count() function doubt [message #206654 is a reply to message #206629] Fri, 01 December 2006 00:31 Go to previous messageGo to next message
rafirshaik_apps
Messages: 3
Registered: October 2006
Junior Member
hi,
have u tried this??
select count(1) from test123;
and
select count(*) from test123;
both will return the same answer..the differece is..
count(1) has to replace the all values with 1 and returns the count...where as count(*) simply returns the no.of rows present in the table..so we can conclude that,count(*) is faster than count(1)...and 1 does not represent column number as vp17in(sorry,i dont know his name) said.if u give count(2),then also it returns the same as the count(1) does.hope u understood.
regards
rafi.
Re: count() function doubt [message #206657 is a reply to message #206654] Fri, 01 December 2006 00:45 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
count(1) has to replace the all values with 1 and returns the count


Why do you think so?
Have you checked my previous post?

By
Vamsi
icon14.gif  Re: count() function doubt [message #206674 is a reply to message #206629] Fri, 01 December 2006 01:16 Go to previous messageGo to next message
vp17in
Messages: 12
Registered: November 2006
Junior Member
Thank you very much vamsi
Re: count() function doubt [message #206676 is a reply to message #206657] Fri, 01 December 2006 01:18 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
For a detailed discussion on Ask Tom:

http://asktom.oracle.com/pls/ask/f?p=4950:8:9841722601267600680::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1156159920245

(couldn't bring myself to reading the whole thing, but I'm pretty sure you'll find everything you have always wanted to know about count(*) vs count(1), but were afraid to ask...)
Previous Topic: USING DYNAMIC SQL within PL/SQL
Next Topic: database desing concepts
Goto Forum:
  


Current Time: Fri Dec 09 05:52:22 CST 2016

Total time taken to generate the page: 0.25553 seconds