Group by Clause in MySQL and Postgresql, Why the Error in Postgresql

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

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;

PostgreSQL GROUP BY different from MySQL?

MySQL's totally non standards compliant GROUP BY can be emulated by Postgres' DISTINCT ON. Consider this:

MySQL:

SELECT a,b,c,d,e FROM table GROUP BY a

This delivers 1 row per value of a (which one, you don't really know). Well actually you can guess, because MySQL doesn't know about hash aggregates, so it will probably use a sort... but it will only sort on a, so the order of the rows could be random. Unless it uses a multicolumn index instead of sorting. Well, anyway, it's not specified by the query.

Postgres:

SELECT DISTINCT ON (a) a,b,c,d,e FROM table ORDER BY a,b,c

This delivers 1 row per value of a, this row will be the first one in the sort according to the ORDER BY specified by the query. Simple.

Note that here, it's not an aggregate I'm computing. So GROUP BY actually makes no sense. DISTINCT ON makes a lot more sense.

Rails is married to MySQL, so I'm not surprised that it generates SQL that doesn't work in Postgres.

How to fix syntax errors on GROUP BY clause when converting MySQL to PostgreSQL

This line is missing a closing parenthesis:

AND extract(epoch from NOW()) BETWEEN extract(epoch from NOW()- INTERVAL '12 months') AND to_date(NOW()::TEXT

It also doesn't make any sense, but the syntax error is did to the missing closing parenthesis.

Now will always be between now and twelve months ago. Casting now to text and converting to a date is an extra step at best, but you are clearly missing some part of the logic after that.

Postgresql error: column must appear in the GROUP BY clause or be used in an aggregate function

I would suggest distinct on:

select distinct on ( case when receiver = 'user1' then sender else receiver end ) m.*
from messages m
where 'user1' in (receiver, sender)
order by (case when receiver = 'user1' then sender else receiver end), time desc;

postgres group by error

As i dont see any need for group by clause in your query, try this query:

SELECT * 
FROM user_ips 
WHERE IP = '%s' 
ORDER BY datetime

GROUP BY should be used when you are trying to say grouping records by say ip addresses and you are trying to apply aggregate functions likes MIN/MAX/SUM/AVG etc.

GROUP BY query that works in MySQL is rejected by PostgreSQL

The error explains it: you need to use an aggregate function to make this deterministic, eg:

SELECT units.*, max(activity_logs.created_at)
FROM "units" left join activity_logs on units.id=activity_logs.unit_id 
GROUP BY units.id 
ORDER BY max(activity_logs.created_at) desc;

---

Related Topics