Geek asks and answers

Can I use the column that I selected later in the query?

Submit this request ...

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

As you can see, I repeated adding hitsand other_hits. Can I refer to the column total_hitsthat I created in other parts of the query?

I tried and I got 1054: Unknown column in the where section.

+3
source share
5 answers

Using:

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

Earliest MySQL allows column aliases to be referenced - this is a sentence GROUP BY; after support links ( HAVING, ORDER BY). Most other databases do not support the reference to the table alias before ORDER BY, which usually requires the use of a view / 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

WHERE:

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

WHERE .

:

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

, . , SELECT , WHERE. , , , :

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

, SELECT . , , .

+1

HAVING, .

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

Again, there will be performance problems, since the calculation will be performed for the entire table before the filter.

+1
source

Add a column to the table named total_hits, then define INSERT and UPDATE triggers to calculate the value of the column when you insert a row. Then you can simply do this:

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

This has the added benefit of being able to index for a very fast search over the computed column in your query.

0
source

More articles:


All Articles