Implicit Aggregation

The investors of devils.fyi want their quarterly growth report! We need to show them how many users we have now. We can determine this by counting the number of rows in the employees table. There is the count() function to count values, but it counts within groups. We just want to treat the entire table as 1 big group. So, we can pass an empty value to GROUP BY.

SELECT count(name)
FROM employees
GROUP BY ();

Conceptually, nothing new or special is happening here. It is the same thing happening as when we first looked at GROUP BY. Remember this diagram from when we did GROUP BY company?

All of the columns except company became these red columns where their values turned into groups of values, and they grouped together if they have the same value in the green column, company.

When we group by the empty value, all of the columns form these groupings.

Since there is no value to separate the groups, the whole table is 1 big group, and the grouped values are in 1 row. That means when we start SELECTing these value using aggregate functions, the result will always contain 1 row.

Converting to a Widely Used SQL Idiom

The above query works. However, there are a couple of things we should change. First, GROUP BY () is never actually necessary. If the query contains any aggregate functions in the SELECT clause, you can omit the GROUP BY clause altogether and it will treat the whole table as 1 group. Second, remember how NULL works with count? Since we're doing count(name), we would miscount if for some reason there were users with a NULL name. There is a shortcut to just count the number of rows without regard for NULL values: count(*).

So, we can rewrite that query like this:

SELECT count(*)
FROM employees;