ROLLUP with ORDER BY in MySQL

user_14923859 0
Viewed 545

I have a problem with query in mysql. My Query:

SELECT year, person, SUM(amount) AS total_amount FROM rentals
GROUP BY year, person WITH ROLLUP
ORDER BY year DESC;

Gives me in response error like this:

Error Code: 1221. Incorrect usage of CUBE/ROLLUP and ORDER BY

I've read in other posts in StackOverFlow that in MySQL there can be a problem to use ROLLUP and ORDER BY in one query and I know that I can make this using subquery, but the point is that maybe one month ago I could make this query and yesterday the problem has started. Some days ago I turned on ONLY FULL GROUP BY mode, can it be the reason? What can I do to be able to use the rollup with order by in one query as previously? I would like to make that I don't have to use subqueries because I know that there is some possibility to make this (like previously).

rollup subquery sql-order-by mysql sql
1 Answers

Your error message comes from MySQL 5.7 or earlier. This construct you can use only in MySQL 8

CREATE tABLE rentals(year int , person varchar(20), amount BIGINT )

SELECT year, person, SUM(amount) AS total_amount FROM rentals
GROUP BY year, person WITH ROLLUP
ORDER BY year DESC;

year | person | total_amount
---: | :----- | -----------:

db<>fiddle here

IN MYSQL 5.7 You nned to run

CREATE tABLE rentals(year int , person varchar(20), amount BIGINT )

āœ“

SELECT * 
FROM 

(SELECT year, person,SUM(amount)
FROM rentals
GROUP BY year, person WITH ROLLUP) t1
ORDER BY year DESC;

year | person | SUM(amount)
---: | :----- | ----------:

db<>fiddle here

user_51935360
Related