选择并组合在一起 [英] Select and Group by together

问题描述

我有这样的查询：

Select 
  a.abc,
  a.cde,
  a.efg,
  a.agh,
  c.dummy
  p.test
  max(b.this)
  sum(b.sugar)
  sum(b.bucket)
  sum(b.something)

其次是一些外连接和内连接。现在的问题是在组中由

followed by some outer join and inner join. Now the problem is when in group by

group by 
  a.abc,
  a.cde,
  a.efg,
  a.agh,
  c.dummy,
  p.test   

查询工作正常。但是，如果我从组中删除任何一个，它会给出：

The query works fine. But if I remove any one of them from group by it gives:

SQLSTATE: 42803

任何人都可以解释这个错误的原因？

Can anyone explain the cause of this error?

推荐答案

通常，部分中不在组中的任何列只能包含在 select 部分，如果它具有应用的聚合功能。或者，另一种方式是，选择部分中的任何非汇总数据必须分组。

Generally, any column that isn't in the group by section can only be included in the select section if it has an aggregating function applied to it. Or, another way, any non-aggregated data in the select section must be grouped on.

Otherewise，你怎么知道你想要做什么？例如，如果您在 a.abc 上分组，则只能有一件事情， a.abc 可以该分组行（因为 a.abc 的所有其他值将在不同的行中出现）。以下是一个简短的例子，其中包含一个表：

Otherewise, how do you know what you want done with it. For example, if you group on a.abc, there can only be one thing that a.abc can be for that grouped row (since all other values of a.abc will come out in a different row). Here's a short example, with a table containing:

LastName  FirstName  Salary
--------  ---------  ------
Smith     John       123456
Smith     George     111111
Diablo    Pax        999999

使用查询选择姓氏，员工薪资由LastName 组合，您将看到：

With the query select LastName, Salary from Employees group by LastName, you would expect to see:

LastName  Salary
--------  ------
Smith     ??????
Diablo    999999

史密斯的薪水是无法估量的，因为你不知道什么功能应用于它，这是导致该错误的原因。换句话说，DBMS不知道如何使用 123456 和 111111 来获取单个值分组行

The salary for the Smiths is incalculable since you don't know what function to apply to it, which is what's causing that error. In other words, the DBMS doesn't know what to do with 123456 and 111111 to get a single value for the grouped row.

如果您改为使用，请选择LastName，Employees组中的Sum（Salary）由LastName （或 max（）或 min（）或 ave（）任何其他聚合功能），DBMS将会知道该怎么做。对于 sum（），它将只是添加它们，并给你 234567 。

If you instead used select LastName, sum(Salary) from Employees group by LastName (or max() or min() or ave() or any other aggregating function), the DBMS would know what to do. For sum(), it will simply add them and give you 234567.

在您的查询中，相当于尝试使用 Salary 而不使用聚合函数来更改 sum（b.this）只是 b.this ，但不包含在组中的部分。或者，通过列删除其中一个列，而不将其更改为 select 部分中的聚合。

In your query, the equivalent of trying to use Salary without an aggregating function is to change sum(b.this) to just b.this but not include it in the group by section. Or alternatively, remove one of the group by columns without changing it to an aggregation in the select section.

在这两种情况下，您将有一行具有该列的多个可能的值。

In both cases, you'll have one row that has multiple possible values for the column.

sqlstate 42803中的DB2文档为sqlstate 42803描述您的问题：

The DB2 docs at publib for sqlstate 42803 describe your problem:

SELECT或HAVING子句无效，因为它不是分组列;或者GROUP BY子句中的列引用无效。

A column reference in the SELECT or HAVING clause is invalid, because it is not a grouping column; or a column reference in the GROUP BY clause is invalid.

这篇关于选择并组合在一起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！