Can I use a column I have selected later in a query?

Imagine this query...

SELECT `id`,
       `hits` + `other_hits` AS `total_hits`
  FROM `something`
 WHERE `hits` + `other_hits` > 30

As you can see, I've repeated the addition of hits and other_hits. Can I refer to total_hits column I created in other parts of the query?

I tried it, and I got 1054: Unknown column in where clause.开发者_运维知识库

Use:

SELECT `id`,
       `hits` + `other_hits` AS `total_hits`
  FROM `something`
HAVING `total_hits` > 30

The earliest MySQL allows references to column aliases is the GROUP BY clause; clauses after that support references (HAVING, ORDER BY). Most other databases don't support referencing a table alias before the ORDER BY, which typically requires using a derived table/inline view:

SELECT t.id, t.total_hits
  FROM (SELECT `id`,
               `hits` + `other_hits` AS `total_hits`
          FROM `something`) t
 WHERE t.total_hits > 30

Otherwise, you have to reuse the logic in the WHERE clause:

SELECT `id`,
       `hits` + `other_hits` AS `total_hits`
  FROM `something`
 WHERE `hits` + `other_hits` > 30

You have to refer to the formula, not the column name. The column name doesn't get evaluated until the SELECT statement gets evaluated, which is AFTER the WHERE statement. Unfortunately, you are going to need to repeat the statement twice like you have done unless you were to wrap the statement like so:

SELECT *
FROM (
SELECT `id`,
       `hits` + `other_hits` AS `total_hits`
  FROM `something`) as t
 WHERE `total_hits` > 30

Notice the performance problem though in that your inner SELECT gets evaluated on every item. This might cause a problem for you or it might not, depending on your table design.

You can use the calculated variable in the HAVING clause as this is evaluated after the select.

SELECT `id`,
       `hits` + `other_hits` AS `total_hits`
  FROM `something`
 GROUP BY `id`, `total_hits`
 HAVING `total_hits` > 30

Again, there will be performance issues as the calculation will be done for the whole table before being filtered.

You can't use the WHERE clause to reference column aliases.

You can try:

SELECT t.*
FROM (
  SELECT `id`, `hits` + `other_hits` AS `total_hits`
  FROM `something`) t
WHERE t.`total_hits` > 30

Add a column to your table named total_hits, then define INSERT and UPDATE triggers to calculate the column value when a row is inserted. Then you could just do this:

SELECT
  `id`, `total_hits`
FROM `something`
WHERE `total_hits` > 30;

This has the added advantage of being able to be indexed for very fast retrieval versus a calculated column in your query.