Unknown column ‘r’ in field list

I’ve been working on a SQL query for a project, and I face an error message when I want to use it.

Here is the query itself :

SELECT  COUNT(r)                                AS  auditMade,
        SUM(g.nbrMilkingCows)                   AS  cowsAudited,
        AVG(r.gainPerCowPerYearTransition)      AS  averageGainTransition,
        AVG(r.gainPerCowPerYearLactation)       AS  averageGainLactation,
        AVG(r.totalGain)                        AS  averageTotalGain,
        AVG(r.supplementalCostPerCow)           AS  averageSuppCost
FROM        `smart_calculator_infos` i
INNER JOIN  `smart_calculator_result` r             ON  r.idSmartCalculatorResult = i.idSmartCalculatorResult
INNER JOIN  `calculator_general_informations` g     ON  g.idSmartCalculatorInfo = i.idSmartCalculatorInfo
WHERE   i.idUser = 14

MySQL answers me “Unknown column ‘r’ in field list”.

But I dont really understand why I get an error here as I define r in my INNER JOIN.

I’m kinda new at using SQL so maybe there is something pretty obvious I forgot, but I can’t seem to understand what.

Answer

You can’t count an alias itself, so the very first line of your query is what is causing the error:

SELECT COUNT(r)

To remedy this, you could use COUNT(*):

SELECT COUNT(*)

Or, you could count an actual column in the smart_calculator_result table, e.g.

SELECT COUNT(r.idSmartCalculatorResult)