By bread


2010-06-09 06:14:16 8 Comments

Does it make a difference if you do count(*) vs count(column-name) as in these two examples?

I have a tendency to always write count(*) because it seems to fit better in my mind with the notion of it being an aggregate function, if that makes sense.

But I'm not sure if it's technically best as I tend to see example code written without the * more often than not.

count(*):

select customerid, count(*), sum(price) 
from items_ordered
group by customerid
having count(*) > 1;

vs. count(column-name):

SELECT customerid, count(customerid), sum(price)
FROM items_ordered
GROUP BY customerid
HAVING count(customerid) > 1;

5 comments

@gbn 2010-06-09 06:29:53

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

@nickf 2010-06-09 07:51:10

there is a performance difference (at least in MySQL) as well (see my answer).

@PureSilence 2019-10-03 07:49:41

To be exact, COUNT(column) counts rows in which values of column is not NULL, whereas COUNT(*) counts all rows of the table.

@Tommi 2010-06-09 06:16:57

Yes, there is possible difference in performance. Depending on your query, and the indexing of the table in question, it can be quicker to get the count from the index instead of going to table for the data. Thus you probably should specify the field name, instead of using *.

@Dean Harding 2010-06-09 06:20:54

When it's an identifier (and guaranteed to be non-NULL) then it probably doesn't matter.

However, there is a difference between COUNT(*) and COUNT(column) in general, in that COUNT(column) will return a count of the non-NULL values in the column. There is also the COUNT(DISTINCT column) variant which returns the number of unique, non-NULL values.

@nickf 2010-06-09 06:19:55

This applies to MySQL. I'm not sure about the others.

The difference is:

  • COUNT(*) will count the number of records.
  • COUNT(column_name) will count the number of records where column_name is not null.

Therefore COUNT(*) is what you should use. If you're using MyISAM and there is no WHERE clause, then the optimiser doesn't even have to look at the table, since the number of rows is already cached.

@Shane N 2014-07-02 18:07:06

So I understand that count(col) is slower than count(*) because MySQL has to examine the rows to see if NULL, but if the column is defined as NOT NULL, then does it still matter?

@Stphane 2015-08-28 13:47:20

What about count( col ) where col is a primary indexed column ? I thaught indexes help making tasks faster ?

@Matthieu 2019-09-03 08:00:52

@Stphane in that case it will use the index.

@zed_0xff 2010-06-09 06:17:42

Generally it's the same, but in details AFAIK "count(*)" is better b/c "count(columnname)" forces DB to execute a little more code to lookup that column name (but not necessary though).

@Osama Al-Maadeed 2010-06-09 06:23:40

It depends on the database, Oracle/MSSQL/MySQL/DB2 do it differently.

Related Questions

Sponsored Content

21 Answered Questions

41 Answered Questions

[SOLVED] Table Naming Dilemma: Singular vs. Plural Names

37 Answered Questions

19 Answered Questions

[SOLVED] Count the number occurrences of a character in a string

  • 2009-07-20 20:00:36
  • Mat
  • 904505 View
  • 901 Score
  • 19 Answer
  • Tags:   python string count

8 Answered Questions

[SOLVED] Best practices for SQL varchar column length

22 Answered Questions

[SOLVED] How can I count the occurrences of a list item?

  • 2010-04-08 13:30:00
  • weakish
  • 1472780 View
  • 1391 Score
  • 22 Answer
  • Tags:   python list count

24 Answered Questions

[SOLVED] Find all tables containing column with specified name - MS SQL Server

2 Answered Questions

[SOLVED] Using group by on multiple columns

  • 2010-03-10 23:11:23
  • l--''''''---------''''''''''''
  • 954278 View
  • 954 Score
  • 2 Answer
  • Tags:   sql group-by

11 Answered Questions

[SOLVED] How to use count and group by at the same select statement

  • 2010-04-27 15:15:17
  • Stavros
  • 646706 View
  • 209 Score
  • 11 Answer
  • Tags:   sql count group-by

Sponsored Content