Postgresql 9.3. Group without all columns
I am having a problem with the following request:
SELECT
ee.id
ee.column2
ee.column3,
ee.column4,
SUM(ee.column5)
FROM
table1 ee
LEFT JOIN table2 epc ON ee.id = epc.id
WHERE
ee.id (6050)
GROUP BY ee.id
The WHERE column identifier is the primary key. In version 8.4, the query returns an error stating that columns2, columns3, and columns4 do not exist in the group by clause.
The same request succeeds on version 9.3.
Does anyone know why?
source to share
This was introduced in 9.1
Quoting from the release note :
Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause (Peter Eisentraut)
The SQL standard allows this behavior, and because of the primary key, the result is unambiguous.
This is also explained by examples in the chapter on group by
:
In this example, the columns product_id, p.name, and p.price must be in the GROUP BY clause because they are listed in the query select list (but see below). The s.units column does not have to be in the GROUP BY list because it is only used in the aggregate expression (sum (...)), which represents the sale of a product. For each product, the query returns a summary row of all sales for the product.
In short: if a sentence group by
contains a column that uniquely identifies rows, you only need to include that column.
source to share
The SQL-99 standard introduces the concept of functionally dependent columns. A column is functionally dependent on another column when that other column (or set of columns) already uniquely identifies it. Therefore, if you have a table with a primary key, all other columns in that table functionally depend on that primary key.
This way, when using GROUP BY
and you include the primary key of a table, you do not need to include other columns of the same table in the GROUP BY
-clause as they have already been uniquely identified by the primary key.
This is also described in the GROUP BY
article :
If present,
GROUP BY
or any aggregated functions are present, it isSELECT
not valid for list expressions to reference non-grouped columns except inside aggregate functions, or when the non-grouped column functionally depends on grouped columns, since there would otherwise be more than one possible value to return in ungrouped column. A functional dependency exists if the grouped columns (or a subset of them) are the primary key of the table that contains the ungrouped column.
(emphasis mine)
source to share