MySQL Unknown column in having clause

Question

I'm trying to get all the wins per team, however, SQL decides to throw an error

enter image description here

The following query is being executed:

SELECT `t`.`teamcode`, COUNT(*) AS `gewonnen` FROM `Team` `t` INNER JOIN `Wedstrijd` `w` ON `w`.`teamthuis` = `t`.`teamcode` GROUP BY `w`.`teamthuis` HAVING `w`.`scorethuis` > `w`.`scoreuit` #1054 - Unknown column 'w.scorethuis' in 'having clause'

Without aliases:

SELECT `Team`.`teamcode`, COUNT(*) AS `gewonnen` FROM `Team` INNER JOIN `Wedstrijd` ON `Wedstrijd`.`teamthuis` = `Team`.`teamcode` GROUP BY `Wedstrijd`.`teamthuis` HAVING `Wedstrijd`.`scorethuis` > `Wedstrijd`.`scoreuit` #1054 - Unknown column 'Wedstrijd.scorethuis' in 'having clause'

Are you sure that Wedstrijd has a column with the name scorethuis? — Mahmoud Gamal
– Mahmoud Gamal, Commented Dec 15, 2013 at 14:44
Apart from the fact that the having clause does not do what you think it does, is the column scorethuis or scorehuis? — Gordon Linoff
– Gordon Linoff, Commented Dec 15, 2013 at 14:48
Do not understand the downvote here. I'm stumped by this error too. Was really curious on the reason behind it. — DBC
– DBC, Commented Dec 15, 2013 at 15:11

Mahmoud Gamal · Accepted Answer · 2013-12-15 14:48:04Z

13

There is no need to use HAVING. Try WHERE instead:

SELECT `t`.`teamcode`, COUNT(*) AS `gewonnen` FROM `Team` `t` INNER JOIN `Wedstrijd` `w` ON `w`.`teamthuis` = `t`.`teamcode` WHERE `w`.`scorethuis` > `w`.`scoreuit` GROUP BY `w`.`teamthuis`

answered Dec 15, 2013 at 14:48

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mazzy Over a year ago

Yep that's it, thanks, could you explain why having doesn't work in this case?

Mahmoud Gamal Over a year ago

@Mazzy I think this is because the column scorethuis is not included in the GROUP BY, the error is misleading actually :)

Mazzy Over a year ago

I believe it is because I'm performing scorethuis > scoreuit on EVERY row and not on the actual group :)

ucb Over a year ago

This solution only worked for me, but only when I had the WHERE clause before the GROUP BY clause.

David Ansermot · Accepted Answer · 2016-05-13 07:40:21Z

I think if you had select w.scorethuis w.scoreuit both in select statement then 'Having;' would work. But I faced the same problem and I resolved it by the above way.

SELECT `t`.`teamcode`, COUNT(*),***`w`.`scorethuis`, `w`.`scoreuit`*** AS `gewonnen` FROM `Team` `t` INNER JOIN `Wedstrijd` `w` ON `w`.`teamthuis` = `t`.`teamcode` GROUP BY `w`.`teamthuis` HAVING `w`.`scorethuis` > `w`.`scoreuit`

Collectives™ on Stack Overflow

MySQL Unknown column in having clause

2 Answers 2

4 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Linked

Related