Question 21866 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL statement not valid – Mysql

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

Tags:

2

Answers


  1. 0

    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"
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search