Select and Group Together

Question

I have my request:

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 that in the group

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

The request is working fine. But if I remove one of them from the group, he will give:

 SQLSTATE: 42803 

Can someone explain the reason for this error?

Answer 1

As a rule, any column that is not in the group by section can only be included in the select section if it has an aggregation function applicable to it. Or, otherwise, any non-aggregated data in the select section should be grouped.

Regarding how you know what you want to do with it. For example, if you are grouping in a.abc , there can only be one thing for which a.abc can be for this grouped row (since all other a.abc values ​​will go on another row). Here is a short example containing a table containing:

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

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

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

The salary for the Smiths is innumerable, since you do not know which function to apply to it, which is the cause of this error. In other words, the DBMS does not know what to do with 123456 and 111111 to get one value for a grouped row.

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

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

In both cases, you will have one row with several possible values ​​for the column.

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

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

Answer 2

SQL will insist that any column in the SELECT section is either included in the GROUP BY section or has an aggregate function applied to it in the SELECT section.

This article gives a good explanation of why this is so. The article is specific to the SQL server, but the principle should be approximately the same for all DBMS