What is the difference between count (*) and count(attribute_name)?

Question

Is there any difference between COUNT(*) and COUNT(attribute_name)?

I used count(attribute_name) as I thought that it would be specific hence the searching process would be easier. Is that true?

It would be great to see any example with sql code with my issue to help me understand better

Count(*) will return number of rows in the table. Count(colname) will return number of non null rows for that particular column — Somnath Ghosh, Nov 06 '19 at 08:40
Possible duplicate of [Count property vs Count() method?](https://stackoverflow.com/questions/7969354/count-property-vs-count-method) — Ed Bangga, Nov 06 '19 at 08:46

score 3 · Accepted Answer · answered Nov 06 '19 at 08:43

3

Imagine this table:


select Count(TelephoneNumber) from Calls -- returns 3
select Count(*) from Calls -- returns 4

count(column_name) also counts duplicate values. Consider:

select Count(TelephoneNumber) from Calls -- returns 4

answered Nov 06 '19 at 08:43

wserr

436
1
3
12

I understand your explanation the most! Thank you – newbeginnner Nov 06 '19 at 09:11

score 3 · Answer 2 · answered Nov 06 '19 at 08:43

COUNT(*) counts all the records in the group.

COUNT(column_name) only counts non-null values.

There is also another typical expression, COUNT(DISTINCT column_name), that counts non-null distinct values.

Since you asked for it, here is a demo on DB Fiddlde:

with t as (
    select 1 x from dual
    union all select 1 from dual
    union all select null from dual
)
select count(*), count(x), count(distinct x) from t

COUNT(*) | COUNT(X) | COUNT(DISTINCTX)
-------: | -------: | ---------------:
       3 |        2 |                1

Andrew · Answer 3 · 2019-11-06T09:08:54.560

2

COUNT(*) will count all the rows. COUNT(column) will count non-NULLs only.

Your can use of COUNT(*) or COUNT(column) which should be based on the desired output only.

Consider below Example of employee table

ID        Name      Description
1         Raji      Smart
2         Rahi      Positive
3 
4         Falle     Smart

select count(*) from employee;

Count(*)
4

select count(name) from employee;

Count(Name)
3

edited Nov 06 '19 at 09:08

answered Nov 06 '19 at 08:39

Andrew

3,632
24
64
113

count(Column Name) counts duplicate rows. – jarlh Nov 06 '19 at 08:41
added example for avoiding confusion – Andrew Nov 06 '19 at 08:44
so that means if I use count(*), it will count all data in the table even if the row has no data. Right? – newbeginnner Nov 06 '19 at 08:57
this explanation is kinda confusing – newbeginnner Nov 06 '19 at 08:58
yes exactly count(*) will count row with null values also where as count(column) doesnt..what is confusing please let me know...i have provided very simple example..if you have any example i can explain much better.. – Andrew Nov 06 '19 at 09:02
okay thank you! I need some time to understand this concept. I will try to do some exercise to make it clear for me – newbeginnner Nov 06 '19 at 09:14

score 1 · Answer 4 · answered Nov 06 '19 at 08:41

1

count() only counts non-null values. * references the complete row and as such never excludes any rows. count(attribute_name) only counts rows where that column is no null.

So this:

select count(attribute_name)
from the_table

is equivalent to:

select count(*)
from the_table
where attribute_name is not null

answered Nov 06 '19 at 08:41

so that means if I use count(attribute_name), it will count all data in the table even if the row has no data. Right? – newbeginnner Nov 06 '19 at 08:58
No, the opposite: It will count all rows where the _column_ **does** contain data. Look at the second query – Nov 06 '19 at 08:59

score -1 · Answer 5 · answered Nov 06 '19 at 08:44

The difference is simple: COUNT(*) counts the number of rows produced by the query, whereas COUNT(1) counts the number of 1 values. Note that when you include a literal such as a number or a string in a query, this literal is "appended" or attached to every row that is produced by the FROM clause.

For more detail this link would help you understand.

What is the difference between count (*) and count(attribute_name)?

5 Answers5