2

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?

Community
  • 1
  • 1
Amelio Vazquez-Reina
  • 91,494
  • 132
  • 359
  • 564
  • 3
    If you want to run the statement, then the `group by` is needed. What is your question? – Gordon Linoff Feb 20 '14 at 14:06
  • `COUNT` is an aggregate function. You need `GROUP BY` to execute it. Without it, this is not only won't execute, it's becoming meaningless – Alexander Feb 20 '14 at 14:07
  • In your update the statement only returns a single row. The `COUNT` over the whole table. Now if you add `column_a` to the `SELECT` list which one of the possibly many values should be returned in that single row? – Martin Smith Feb 20 '14 at 14:11

4 Answers4

7

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.

exhuma
  • 20,071
  • 12
  • 90
  • 123
1

When you have a function like count, sum etc. you need to group the other columns. This would be equivalent to your query:

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

When you use count(*) with no other column, you are counting all rows from SELECT * from the table. When you use count(*) alongside another column, you are counting the number of rows for each different value of that other column. So in this case you need to group the results, in order to show each value and its count only once.

Rob Bednark
  • 25,981
  • 23
  • 80
  • 125
Guilherme Vaz
  • 380
  • 1
  • 2
  • 14
1

If you don't use the Group by clause at all then all that will be returned is a count of 1 for each row, which is already assumed anyway and therefore redundant data. By adding GROUP BY 1 you have categorized the information thereby making it non-redundant even though it returns the same result in theory as the statement that creates an error.

Web Develop Wolf
  • 5,996
  • 12
  • 52
  • 101
1

group by 1 in this case refers to column_a which has the column position 1 in your query.

This why it works on your server. Indeed this is not a good practice in sql. You should mention the column name because the column order may change in the table so it will be hard to maintain this code.

The best solution is:

select column_a, count(*) from my_schema.my_table group by column_a;
Rob Bednark
  • 25,981
  • 23
  • 80
  • 125
Aymanadou
  • 1,200
  • 1
  • 14
  • 36