Databases SQL Reporting by Example Day 3: Getting Good at Grouping Busiest Teachers

lukej
lukej
32,714 Points

The meaning of COUNT(1)?

I am puzzled as to what the meaning of COUNT(1) is? It counts the first column or a column named 1?

I just avoid it by using COUNT(*), or COUNT(period_id) in this video

2 Answers

Steven Parker
Steven Parker
177,674 Points

The "COUNT" function counts every row for which the supplied value is not null. So there's no difference between providing a "*", a literal digit, or an ID field. The number "1" is just a common convention used used to mean "count all rows".

But if you supply a field name that can contain nulls, rows where that field is null will not be counted. For examples, using the playground from this video:

SELECT COUNT(1) FROM subjects;      /* 24 (all rows) */
SELECT COUNT(name) FROM subjects;   /* 24 (no nulls) */
SELECT COUNT(grade) FROM subjects;  /* 18 (because of nulls) */
Tommy Gebru
Tommy Gebru
29,403 Points

I also keep assuming it to be either the first column of the table or the first parameter of the SELECT statement