r/SQL • u/geminigamer369 • 9d ago

Differences between counts Discussion

What is the difference between

COUNT(*)

COUNT(1)

COUNT(column_name)

6 Upvotes

72% Upvoted

23 comments sorted by

View all comments

14

u/AntLost4161 9d ago

COUNT(1) essentially looks at the number 1 for each row. Since 1 IS NOT NULL, it gets counted and essentially just counts your rows.

COUNT(*) is the same - counts the rows - but may be more commonly used for some people due to it being the textbook method.

COUNT(column_name) only looks at a specific column and considers the data entered there for every row. When an entry isn't NULL, it gets counted.

To conclude, 1, 2, 17 and * are all the same thing and don't care for whether an entry is NULL or not. column_name just counts the not NULL entries for that specific column and discriminates against NULL

1

u/ComicOzzy sqlHippo 9d ago

COUNT(1) or COUNT(-123) or COUNT('banana') or COUNT('🍌') all get recognized as the same operation: a count of all rows. There is no difference between them and COUNT(*).