是否有可能在Select语句中使用Aggregate函数而不使用Group By子句？ [英] Is it possible to use Aggregate function in a Select statment without using Group By clause?

问题描述

到目前为止，我已经编写了聚合函数，后面跟着Group By子句，以查找基于SUM，AVG和其他聚合函数的值。我在Group By子句中有点混乱。
当我们使用聚合函数时，我需要在Group By子句中指定哪些列。
否则是否有任何方法可以在不使用Group By子句的情况下使用聚合函数。

解决方案

SELECT子句中的所有列没有聚合需要在GROUP BY中

好：

  SELECT col1，col2，col3，MAX（col4）
 ... 
 GROUP BY col1，col2，col3 
  

同样好：

  SELECT col1，col2，col3，MAX（col4） 
 ... 
 GROUP BY col1，col2，col3，col5，col6 
  

没有其他列=不需要GROUP BY

  SELECT MAX（col4）
 ... 
  

无效：

  SELECT col1，col2，col3，MAX（col4）
 ... 
 GROUP BY col1，col2 
  

无意义：

 选择col1，col2，col3，MAX（col4）
 ... 
 GROUP BY col1，col2，col3，MAX（col4）
  

聚合（M AX等）与没有GROUP BY的其他列没有任何意义，因为查询变得模糊。

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.

解决方案

All columns in the SELECT clause that do not have an aggregate need to be in the 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

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.

这篇关于是否有可能在Select语句中使用Aggregate函数而不使用Group By子句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！