"group by" needed in count(*) SQL statement?

Question

The following statement works in my database:

select column_a, count(*) from my_schema.my_table group by 1;

but this one doesn't:

select column_a, count(*) from my_schema.my_table;

I get the error:

ERROR: column "my_table.column_a" must appear in the GROUP BY clause or be used in an aggregate function

Helpful note: This thread: What does SQL clause "GROUP BY 1" mean? discusses the meaning of "group by 1".

Update:

The reason why I am confused is because I have often seen count(*) as follows:

select count(*) from my_schema.my_table

where there is no group by statement. Is COUNT always required to be followed by group by? Is the group by statement implicit in this case?

Answer 1

This error makes perfect sense. COUNT is an "aggregate" function. So you need to tell it which field to aggregate by, which is done with the GROUP BY clause.

The one which probably makes most sense in your case would be:

SELECT column_a, COUNT(*) FROM my_schema.my_table GROUP BY column_a;

If you only use the COUNT(*) clause, you are asking to return the complete number of rows, instead of aggregating by another condition. Your questing if GROUP BY is implicit in that case, could be answered with: "sort of": If you don't specify anything is a bit like asking: "group by nothing", which means you will get one huge aggregate, which is the whole table.

As an example, executing:

SELECT COUNT(*) FROM table;

will show you the number of rows in that table, whereas:

SELECT col_a, COUNT(*) FROM table GROUP BY col_a;

will show you the the number of rows per value of col_a. Something like:

    col_a  | COUNT(*)
  ---------+----------------
    value1 | 100
    value2 | 10
    value3 | 123

You also should take into account that the * means to count everything. Including NULLs! If you want to count a specific condition, you should use COUNT(expression)! See the docs about aggragate functions for more details on this topic.