how to select years which has more than 2 movies released in mysql-CodePudding

My code is :

SELECT count(*), title, release_year
FROM film
GROUP BY release_year
HAVING COUNT(title) > 2;`

but I got the error: #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sakila.film.title

which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 0.038 sec

CodePudding user response：

Remove title. If you are using a group keywords for x column, only use x column name and sql functions like count, min, max, etc in select statement.

CodePudding user response：

You want to either group by both title and release_year:

SELECT count(*), title, release_year
FROM film
GROUP BY title, release_year
HAVING COUNT(title) > 2;

or just release_year:

SELECT count(*), release_year
FROM film
GROUP BY release_year
HAVING COUNT(title) > 2;

possible adding GROUP_CONCAT(title) to get all titles in for that group in a single row.

CodePudding user response：

To show all tiles the have a count bigger than 2 you can use GROUP _cONCAT and you will not get your error any more

SELECT count(*), GROUP_CONCAT(title SEPARATOR ';'), release_year
FROM film
GROUP BY release_year
HAVING COUNT(title) > 2;`