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

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;

CodePudding user response：

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;