Unknown column 'cleaned.dog_guid' in 'field list'

July 7, 2022

The goal is to output the average number of tests completed by unique dogs in each Dognition personality dimension.
I already put aliases cleaned in front of dog_guid, but the system shows : 1054, "Unknown column ‘cleaned.dog_guid’ in ‘field list’".
Is there anything I have to revise? Thanks a lot.

SELECT cleaned.dog_guid, cleaned.dimension, AVG(cleaned.numtests) AS Avgtests
FROM (SELECT d.dog_guid AS dogID, d.dimension AS dimension, count(c.created_at) AS numtests
      FROM dogs d JOIN complete_tests c
      ON d.dog_guid = c.dog_guid
      GROUP BY dogID) AS cleaned
GROUP BY cleaned.dog_guid, cleaned.dimension;

>Solution :

You aliased dogs.dog_guid as dogID, in the subquery, so you should refer to dogID in the outer query as well.

SELECT cleaned.dogID, cleaned.dimension, AVG(cleaned.numtests) AS Avgtests
FROM
(
    SELECT d.dog_guid AS dogID, d.dimension,
           COUNT(c.created_at) AS numtests
    FROM dogs d
    INNER JOIN complete_tests c ON d.dog_guid = c.dog_guid
    GROUP BY dogID
) AS cleaned
GROUP BY cleaned.dogID, cleaned.dimension;