“分组依据"需要在 count(*) SQL 语句中吗? [英] "group by" needed in count(*) SQL statement?

问题描述

以下语句适用于我的数据库:

The following statement works in my database:

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

但这个没有:

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

我收到错误:

错误:列my_table.column_a"必须出现在 GROUP BY 子句中或在聚合函数中使用

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

有用的说明:这个线程:什么是 SQL子句GROUP BY 1"意思是?讨论group by 1"的含义.

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

我之所以困惑，是因为我经常看到count(*)如下:

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

select count(*) from my_schema.my_table

没有 group by 语句的地方.COUNT 后面总是需要 group by 吗?在这种情况下，group by 语句是否隐含?

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?

推荐答案

这个错误非常有道理.COUNT 是一个聚合"函数.因此，您需要告诉它要聚合的字段，这是通过 GROUP BY 子句完成的.

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;

如果您只使用 COUNT(*) 子句，则您要求返回完整的行数，而不是按其他条件聚合.在这种情况下，您询问 GROUP BY 是否隐含，可以回答为:sort of":如果您不指定任何内容有点像询问:group by nothing"，这意味着您将得到一个巨大的聚合，即整个表.

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.

例如，执行:

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;

将向您显示col_a 的per 值的行数.类似的东西:

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

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

您还应该考虑到* 意味着计算一切.包括NULLs！如果要对特定条件进行计数，则应使用COUNT(expression)！有关此主题的更多详细信息，请参阅有关聚合函数的文档.

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.

这篇关于“分组依据"需要在 count(*) SQL 语句中吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！