3

I have looked into the difference between:

count(*)
count(column_name)
count(1)

For example: here: Count(*) vs Count(1). Some answerers also state: count('foo'). Why would you ever use count('foo'). It seems that this works: 

select count('hello my name is joe bloggs and I finished school about ten years ago') from dbdisposals

Why would you ever pass a string to the count function?

Community
  • 1
  • 1
w0051977
  • 15,099
  • 32
  • 152
  • 329
  • 3
    The datatype of the constant is unimportant. Just that it is `not null` in some ways it might be less confusing than using an integer. E.g. New programmers might not realise that `count(0)` and `count(1)` do exactly the same thing. – Martin Smith May 23 '15 at 13:16
  • possible duplicate of [In SQL, what's the difference between count(column) and count(\*)?](http://stackoverflow.com/questions/59294/in-sql-whats-the-difference-between-countcolumn-and-count) – Denys Séguret May 23 '15 at 13:17
  • 2
    `count(1)`, `count('foo')` and `count('dracula')` are all the same. – Remus Rusanu May 23 '15 at 13:17
  • @dystroy this is probably a dupe but not of that question. – Martin Smith May 23 '15 at 13:19
  • @MartinSmith Of course it is. There's even the answer: "COUNT(*) returns the number of items in a group, including NULL values and duplicates. COUNT(expression) evaluates expression for each row in a group and returns the number of nonnull values." – Denys Séguret May 23 '15 at 13:20
  • 3
    This question isn't about `count(column)` it is about `count('string literal’)` – Martin Smith May 23 '15 at 13:21
  • @MartinSmith a string literal is an expression which never evaluates to null. – Denys Séguret May 23 '15 at 13:21
  • 1
    I'm aware of that. Now go and read the other question and see where it asks about counting constants. Hint it doesn't. – Martin Smith May 23 '15 at 13:22
  • 1
    The question is "Why would you use `count('foo')?`" The answer is that the writer wants to count rows and inject a bit of individuality into the code. I doubt it conforms to any known programming guideline. – Gordon Linoff May 23 '15 at 13:55

1 Answers1

3

Using any literal value in the count has the same effect as count(*), it counts the number of rows in the group. Using count('str') has the same effect as count(1), i.e. it counts the records where the value is not null. As the literal value ('str' or 1) won't be null for any of the records, it counts all the records.

The count function counts all non-null values, so a literal value in count has the same effect as count(*), but count(column_name) counts only the non-null values in that column.

Using a string literal in count works fine, the only reason that you would avoid it would be that it might be confusing.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • Thanks. What does count(1) do? Is it the same as count(column_name), where column_name is the first column? +1. – w0051977 May 23 '15 at 13:21
  • 1
    @w0051977: No, `count(1)` simpy counts the records in the group where the value `1` is not `null`. As the literal value `1` won't be `null` for any of the records, it will count all records, just as `count(*)`. – Guffa May 23 '15 at 13:23
  • @w0051977, the column name specification is semantically the same only of the column is `NOT NULL`. If the column allows nulls and rows contain `NULL` values in the column, the results will be different because the `NULL` values are not counted. There is no good reason to specify a column name when the intent is to count rows rather than non-null values. – Dan Guzman May 23 '15 at 13:51