SQL statement not valid-CodePudding

I have to extract a list froman database based on the following conditions:

"Add below a list that displays the top ten dogs in the show. This is defined as the ten dogs who have the highest average scores, provided they have entered more than one event.Display just the dog’s name, breed, and average score."

This is my implementation but it's not working correctly, I'm getting an error message:

Select name, breed, score
from

(Select dogs.name as name, breeds.name as breed, avg(score) as score, count(breeds.id) as count
from entries
join breeds on entries.dog_id = breeds.id
join dogs on breeds.id = dogs.breed_id
group by breed
order by count)x
where count >= 2
group by breed;

#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'u2167487.dogs.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I looked for solutions but nothing worked out for me

CodePudding user response：

Your query has many problems. You seem to want to aggregate by dog and breed and then filter on the count, so I would just do that directly sans a subquery:

SELECT d.name AS name, b.name AS breed, AVG(score) AS score
FROM entries e
INNER JOIN breeds b ON e.dog_id = b.id
INNER JOIN dogs d ON b.id = d.breed_id
GROUP BY 1, 2
HAVING COUNT(*) >= 2;

The exact error message means you are running MySQL in strict GROUP BY mode, and are selecting non aggregate columns which do not appear in the GROUP BY clause. This is not valid ANSI SQL, hence the error message.

CodePudding user response：

You do a SELECT on three fields - but only one field is in the GROUP BY clause. Add all three fields to the GROUP BY clause or apply aggrigate functions to the fields not included.

When using GROUP BY all your column names must be includede in GROUP BY or have aggrigare functions applied to them.

Select name, breed, score
from
(Select dogs.name as name, breeds.name as breed, avg(score) as score, count(breeds.id) as count
from entries
join breeds on entries.dog_id = breeds.id
join dogs on breeds.id = dogs.breed_id
group by dogs.name as name, breeds.name
order by count)x
where count >= 2
group by name, breed, score;

By maybe in this example you should use the HAVING clause

Select * from 
(
Select dogs.name as name, breeds.name as breed, avg(score) as score
from entries
join breeds on entries.dog_id = breeds.id
join dogs on breeds.id = dogs.breed_id
group by dogs.name as name, breeds.name
having count(*) >= 2
) AS x
ORDER BY "count"