[SOLVED] Unknow column in having clause

Unknow column in having clause - mysql

I know this has been answered, but I can't decipher the solution from the responses given my lack of experience with mysql, so forgive me for asking the same question again. I am converting ms-access queries to mysql and that is a world of hurt for me...the problem I am having is I get the 'Uknown column Big Ditch.Begindate in 'having clause'.

I tried this:

SELECT Sum(datediff(`Big Ditch`.`EndDate`,`Big Ditch`.`BeginDate`)*`Big Ditch`.`Amount`) AS `BigDitchTotal`, extract(year from `Big Ditch`.`BeginDate`) AS Expr1
FROM `Big Ditch`
GROUP BY (extract(year from `Big Ditch`.`BeginDate`))
HAVING (extract(year from `Big Ditch`.`BeginDate`) = current_date());

Solution

Filter the rows by year before doing the aggregation to reduce the amount of data that has to be processed.

MySQL has a built-in YEAR() function that you can use instead of EXTRACT().

You don't need GROUP BY because you're only displaying the result for one year, which is the one you select in the WHERE condition.

SELECT Sum(datediff(`Big Ditch`.`EndDate`,`Big Ditch`.`BeginDate`)*`Big Ditch`.`Amount`) AS `BigDitchTotal`, MAX(YEAR(`Big Ditch`.`BeginDate`)) AS Expr1
FROM `Big Ditch`
WHERE YEAR(`Big Ditch`.`BeginDate`) = YEAR(current_date())