Group by clause in mySQL and postgreSQL, why the error in postgreSQL?

by

You need to use AGGREGATE FUNCTION:

Aggregate functions compute a single result from a set of input
values.

SELECT col2, MIN(col3) AS col3, MIN(col1) AS col1
FROM the_table 
GROUP BY col2;

db<>fiddle demo

MySQL Handling of GROUP BY:

In standard SQL, a query that includes a GROUP BY clause cannot refer
to nonaggregated columns in the select list that are not named in the
GROUP BY clause

and:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate

So with MySQL version without explicit aggregate function you may end up with undetermininistic values. I strongly suggest to use specific aggregate function.

EDIT:

From MySQL Handling of GROUP BY:

SQL92 and earlier does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BY clause.

SQL99 and later permits such nonaggregates per optional feature T301 if they are functionally dependent on GROUP BY columns: If such a relationship exists between name and custid, the query is legal. This would be the case, for example, were custid a primary key of customers.

Example:

SELECT o.custid, c.name, MAX(o.payment)
FROM orders AS o
JOIN customers AS c
  ON o.custid = c.custid
GROUP BY o.custid;

More Related Contents:

  1. Using LIMIT within GROUP BY to get N results per group?
  2. Error related to only_full_group_by when executing a query in MySql
  3. How to filter SQL results in a has-many-through relation
  4. MySQL Query GROUP BY day / month / year
  5. Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs
  6. Retrieving the last record in each group – MySQL
  7. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  8. Mysql query to dynamically convert rows to columns
  9. MySQL “Group By” and “Order By”
  10. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  11. Simple Random Samples from a Sql database
  12. SQL: Repeat a result row multiple times, and number the rows
  13. In SQL, how to select the top 2 rows for each group
  14. SQL Query to get column values that correspond with MAX value of another column?
  15. MySQL Group By and Sum total value of other column
  16. Why do results from a SQL query not come back in the order I expect?
  17. Is it possible to GROUP BY multiple columns using MySQL?
  18. Best practices for SQL varchar column length [closed]
  19. What does SQL clause “GROUP BY 1” mean?
  20. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  21. Using ORDER BY and GROUP BY together
  22. MySQL: How do I join same table multiple times?
  23. Converting SELECT DISTINCT ON queries from Postgresql to MySQL
  24. MySQL GROUP BY DateTime +/- 3 seconds
  25. Are SELECT type queries the only type that can be nested?
  26. how to cast the hexadecimal to varchar(datetime)?
  27. Postgres LEFT JOIN with WHERE condition
  28. MySQL Group By Hours
  29. mysql count group by having
  30. SQL query with avg and group by

Leave a Comment