0

Very new to SQL programming! My company uses MS Access and uses SQL queries like the following:

SELECT RepeatOrder, COUNT(1) FROM [Archive Orders] GROUP BY RepeatOrder

Whenever the COUNT function is used, it is always COUNT(1) and nothing else. I've tested the above query with COUNT(*), COUNT(27) and even COUNT(-1), and they all return exactly the same data.

The w3schools tutorial on SQL COUNT only mentions COUNT(*) as an example (when not using a field name).

What is the significance of a number like "1" in a COUNT function?

AlainD
  • 5,413
  • 6
  • 45
  • 99

1 Answers1

2

They are all the same. COUNT(<expression>) counts the number of non-NULL values of the expression. And 1 and -1 and 27 and 3.1415916535 are all equally not NULL.

The standard is COUNT(*). For some reason, some people are under the mistaken impression that this is not the fastest or best method to just count everything. But it is.

I should note that this is equivalent to SUM(1) . . . with one exception. In a correlated subquery that filters out all rows, this would return NULL whereas COUNT(*) (or COUNT(1) would return 0.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786