SQL 是否可以在 Select 语句中使用 Aggregate 函数而不使用 Group By 子句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6467216/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to use Aggregate function in a Select statment without using Group By clause?
提问by thevan
So far I have written Aggregate function followed by Group By clause to find the values based on SUM, AVG and other Aggregate functions. I have a bit confusion in the Group By clause. When we use Aggregate functions what are the columns I need to specify in the Group By clause. Otherwise Is there any way to use Aggregate functions without using Group By clause.
到目前为止，我已经编写了聚合函数，后跟 Group By 子句来查找基于 SUM、AVG 和其他聚合函数的值。我对 Group By 子句有点困惑。当我们使用聚合函数时，我需要在 Group By 子句中指定哪些列。否则有没有办法在不使用 Group By 子句的情况下使用聚合函数。
回答by gbn
All columns in the SELECT clause that do not have an aggregate need to be in the GROUP BY
SELECT 子句中没有聚合的所有列都需要在 GROUP BY 中
Good:
好的：
SELECT col1, col2, col3, MAX(col4)
...
GROUP BY col1, col2, col3
Also good:
也很好：
SELECT col1, col2, col3, MAX(col4)
...
GROUP BY col1, col2, col3, col5, col6
No other columns = no GROUP BY needed
没有其他列 = 不需要 GROUP BY
SELECT MAX(col4)
...
Won't work:
不会工作：
SELECT col1, col2, col3, MAX(col4)
...
GROUP BY col1, col2
Pointless:
无意义：
SELECT col1, col2, col3, MAX(col4)
...
GROUP BY col1, col2, col3, MAX(col4)
Having an aggregate (MAX etc) with other columns without a GROUP BY makes no sense because the query becomes ambiguous.
将聚合（MAX 等）与没有 GROUP BY 的其他列进行聚合是没有意义的，因为查询变得不明确。
回答by Anthony Sottile
You can use Select AGG() OVER() in TSQL
您可以在 TSQL 中使用 Select AGG() OVER()
SELECT *,
SUM(Value) OVER()
FROM Table
There are other options for Over such as Partition By if you want to group:
如果要分组，还有其他选项，例如 Partition By：
SELECT *,
SUM(Value) OVER(PARTITION By ParentId)
FROM Table
回答by nvogel
Yes you can use an aggregate without GROUP BY:
是的，您可以使用没有 GROUP BY 的聚合：
SELECT SUM(col) FROM tbl;
This will return one row only - the sum of the column "col" for all rows in tbl (excluding nulls).
这将仅返回一行 - tbl 中所有行的“col”列的总和（不包括空值）。
回答by Bohemian
You must group by columns that do nothave aggregate functions on them.
您必须按没有聚合函数的列进行分组。
You may avoid a group by clause if allcolumns selected have aggregate functions applied.
如果选择的所有列都应用了聚合函数，则可以避免使用 group by 子句。
回答by niktrs
You omit columns from the SELECT inside aggregate functions, all other columns should exist in GROUP BY clause seperated by comma.
您在聚合函数中省略了 SELECT 中的列，所有其他列都应存在于 GROUP BY 子句中，以逗号分隔。
You can have query with aggregates and no group by, as long as you have ONLY aggregate values in the SELECT statement
只要在 SELECT 语句中只有聚合值，您就可以使用聚合进行查询而不使用分组依据
回答by Bhawesh Deepak
The Columns which are not present in the Aggregate function should come on group by clause:
聚合函数中不存在的列应该出现在 group by 子句中：
Select
Min(col1),
Avg(col2),
sum(col3)
from table
then we do not required group by clause, But if there is some column which are not present in the Aggregate function then you must use group by for that column.
那么我们不需要 group by 子句，但是如果聚合函数中不存在某些列，那么您必须对该列使用 group by。
Select
col1,
col2,
sum(col3)
from table
group by col1,col2
then we have to use the group by for the column col1 and col2
然后我们必须将 group by 用于列 col1 和 col2
回答by Chandu Reddy
Yes, without aggregate functions we can use group by column but it will be a distinct function.
是的，如果没有聚合函数，我们可以按列使用分组，但这将是一个不同的函数。
select <column name> from <table name> group by <column name>
Given the table EMP:
鉴于表EMP：
select * from EMP
That outputs:
输出：
7369 SMITH CLERK 7902
7499 ALLEN SALESMAN 7698
7521 WARD SALESMAN 7698
7566 JONES MANAGER 7839
Adding a group by
添加一个 group by
select job from EMP group by job
Outputs:
输出：
CLERK
SALESMAN
MANAGER
