1

in SQL, supposes I need to refer to an aliased field in the having clause but the alias has quotes, how do I do that?

select (select...) as '005'
group by ...
having '005'>0

2 Answers2

4

I think you are missing a FROM clause, and you should use backticks instead of single quotes:

SELECT (SELECT ...) AS `005`
FROM table1
GROUP BY ...
HAVING `005` > 0

It would help if you posted your full query, as I am guessing a bit here as to what you want to do.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • 1
    As Mark said the problem is that you are using single quotes rather than back ticks, which makes the alias be treated as a string literal. In the select portion the alias can use normal quotes, but anywhere else it is used you need to use the back ticks. – Waleed Al-Balooshi Jun 05 '10 at 00:18
  • You guys are right, if I use Backticks instead of single quotes the alias works!, thanks –  Jun 05 '10 at 00:24
3

The SQL-92 standard defines using double quotes for a column alias, rather than single quotes. In most databases, unusual characters are only allowed if you use double quotes.

That said, not all databases support referring to a column alias (in the same query) in the GROUP BY or HAVING clauses. For portable queries, I don't recommend the practice of referring to a column alias in the GROUP BY or HAVING clauses. Additionally, the HAVING clause is for aggregates - the simplified example you provided should trigger an error because no aggregate function (IE: COUNT, AVG, MIN/MAX, etc) is performed on the 005 column alias.

The following works for me on MySQL 4.1:

  SELECT COUNT(*) AS "005"
    FROM TABLE t
GROUP BY ...
  HAVING `005` > 0
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502