CODEWITHSUNDEEP
mysqlsqlstringgroup-byhaving-clause
user10575420
user10575420

Reputation:

MySQL HAVING clause not working with ' ' nor " ", instead works without AS statement

SELECT 
    department_id, ROUND(MIN(salary), 2) AS 'Min Salary'
FROM
    employees
GROUP BY department_id
HAVING 'Min Salary' > 800;

This doesn't seem to work, but instead this:

SELECT 
    department_id, ROUND(MIN(salary), 2) AS min_salary
FROM
    employees
GROUP BY department_id
HAVING min_salary > 800

works just fine. Can someone give an answer to why i cant make a HAVING clause with ' ' or " ", but instead i have to use the column name?

Upvotes: 2

Views: 59

Answers (1)

GMB
GMB

Reputation: 222582

HAVING 'Min Salary' > 800

The single quotes around the identifier turn it to a literal string. So this condition is actually checking if string 'Min Salary' is greater than 800. When cast to a number, 'Min Salary' becomes 0, which is smaller than 800. This is not what you expect.

Also, note that MySQL treats double quotes just like single quotes - whereas in some other databases like Oracle or Postgres, they are used to quote identifiers.

If you want to have a identifier that contains a space, then you need to use backticks, which is how MySQL quotes identifiers. So:

SELECT department_id, ROUND(MIN(salary), 2) AS `Min Salary`
FROM employees
GROUP BY department_id
HAVING `Min Salary` > 800;

Upvotes: 2

Related Questions