Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Unknown column in having clause

Tags:

sql

mysql

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'
like image 522
Mazzy Avatar asked Dec 15 '13 14:12

Mazzy


People also ask

Can having clause can be used without group by?

Having can be used without groupby clause,in aggregate function,in that case it behaves like where clause. groupby can be used without having clause with the select statement. 3. The having clause can contain aggregate functions.

What does Unknown column mean in SQL?

The MySQL unknown column in field list error happens when you put a column name in your SQL script that can't be found by MySQL.

Where VS have SQL?

A HAVING clause is like a WHERE clause, but applies only to groups as a whole (that is, to the rows in the result set representing groups), whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause.


1 Answers

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`
like image 189
Mahmoud Gamal Avatar answered Sep 19 '22 02:09

Mahmoud Gamal