Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [duplicate]

by

Suppose I have the following table T:

a   b
--------
1   abc
1   def
1   ghi
2   jkl
2   mno
2   pqr

And I do the following query:

SELECT a, b
FROM T
GROUP BY a

The output should have two rows, one row where a=1 and a second row where a=2.

But what should the value of b show on each of these two rows? There are three possibilities in each case, and nothing in the query makes it clear which value to choose for b in each group. It’s ambiguous.

This demonstrates the single-value rule, which prohibits the undefined results you get when you run a GROUP BY query, and you include any columns in the select-list that are neither part of the grouping criteria, nor appear in aggregate functions (SUM, MIN, MAX, etc.).

Fixing it might look like this:

SELECT a, MAX(b) AS x
FROM T
GROUP BY a

Now it’s clear that you want the following result:

a   x
--------
1   ghi
2   pqr

More Related Contents:

  1. must appear in the GROUP BY clause or be used in an aggregate function
  2. GROUP BY + CASE statement
  3. GROUP BY without aggregate function
  4. Using group by on multiple columns
  5. Aggregate columns with additional (distinct) filters
  6. Create a pivot table with PostgreSQL
  7. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  8. Return multiple columns of the same row as JSON array of objects
  9. Concat groups in SQL Server [duplicate]
  10. Ms Access Query: Concatenating Rows through a query
  11. Best way to count rows by arbitrary time intervals
  12. SQL Query to get column values that correspond with MAX value of another column?
  13. SQL GROUP BY CASE statement with aggregate function
  14. Mysql: how to select groups having certain values?
  15. MySQL #1140 – Mixing of GROUP columns
  16. MySQL: GROUP_CONCAT with LEFT JOIN
  17. Multiple array_agg() calls in a single query
  18. SQL Server Update Group by
  19. Huge performance difference when using GROUP BY vs DISTINCT
  20. SQL: difference between PARTITION BY and GROUP BY
  21. sql group by only rows which are in sequence
  22. SQL not a single-group group function
  23. MySQL GROUP BY behavior
  24. Conditional SQL count
  25. SQL: How do you select only groups that do not contain a certain value?
  26. Count cumulative total in Postgresql
  27. How to get list of values in GROUP_BY clause?
  28. Conversion failed when converting the varchar value ‘simple, ‘ to data type int
  29. Why doesn’t Oracle SQL allow us to use column aliases in GROUP BY clauses?
  30. Why are aggregate functions not allowed in where clause

Leave a Comment