Can you count?

Let’s have a little sequel quiz today, inspired by what I have seen in a real interview.
It will be about COUNT(),our favorite hard-worker among aggregate functions.
Close your IDE or CLI you are using, and try answering yourself first before checking the right answer.

Quiz

Imagine we have a table like this:

| num  | 
| ---- | 
|  1   |
|  2   |
|  3   |
|  4   |
| NULL | 
| NULL |

1. What will you get if you run SELECT COUNT(*) from this table?

2. What will you get if you run SELECT COUNT(num) from this table?

3. What will you get if you run SELECT COUNT('num') from this table?

4. What will you get if you run SELECT COUNT(null) from this table?

5. What will you get if you run SELECT COUNT(#) from this table?

Click to see the answer #1:

| count(*) | 
| -------- | 
|     6    |

Click to see the answer #2:

| count(num) | 
| ---------  | 
|      4     |

Click to see the answer #3:

| count('num') | 
| ------------ | 
|       6      |

Click to see the answer #4:

| count(null) | 
| ----------- | 
|      0      |

Click to see the answer #5:

-- Error: Parser Error: syntax error at or near ")"
-- LINE 1: select count(#) from nums;

Explanation

Well, first one was pretty obvious, wasn’t it? COUNT(*) will give you the count of all rows in the table, in this case: 6.
Pro tip: COUNT(1) ,COUNT(0) ,COUNT(-1) will also give you all rows count: 6.

COUNT(num) counts all non-NULL values in the columns, hence: 4

COUNT('num') will also be 6 because 'num' is a string constant, so same output is produced as in COUNT(1).

COUNT(NULL) will return 0, as NULLs are ignored. \

COUNT(#) is a syntax error.

Try it yourself

This code snippet can help you reproduce the quiz and play with it yourself:

create table nums (num int);
insert into nums values (1),(2),(3),(4),(null),(null);

This is what you get in DuckDB (see my previous post for installation):