count(*) vs count(column-name) - which is more correct?

sql count
bread picture bread · Jun 9, 2010 · Viewed 93.1k times · Source

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;

Answer

gbn picture gbn · Jun 9, 2010

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

Related questions

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

I have an sql select query that has a group by. I want to count all the records after the group by statement. Is there a way for this directly from sql? For example, having a table with users I …

Read More
SQL query for finding records where count > 1

I have a table named PAYMENT. Within this table I have a user ID, an account number, a ZIP code and a date. I would like to find all records for all users that have more than one payment per …

Read More
Fastest way to determine if record exists

As the title suggests... I'm trying to figure out the fastest way with the least overhead to determine if a record exists in a table or not. Sample query: SELECT COUNT(*) FROM products WHERE products.id = ?; vs SELECT COUNT(products.…

Read More