Reference something in the select clause SQL

Question

I was wondering if you can reference something in the where clause like:

select
sum([some calculation]) as x,
sum([some other calculation]) as y,
x/y as z
from 
[rest of the sql...]

Many Thanks

K

If you mean can the `WHERE` clause access the results x, y, and z then the answer is no. They can be accessed in a `HAVING` clause though, which is run on the results of the query before it is returned. As such it is less efficient and so should only be used if there is no better solution. — Rebecka, Apr 16 '13 at 12:28

score 1 · Accepted Answer · answered Apr 16 '13 at 12:27

1

No, you cannot used the alias that was generated on the same level on the SELECT statement.

Here are the possible ways to accomplish.

Using the original formula:

select sum([some calculation]) as x,
       sum([some other calculation]) as y,
       sum([some calculation]) / sum([some other calculation]) as z
from    tableName

or by using subquery:

SELECT  x,
        y,
        x/y z
FROM 
(
   select sum([some calculation]) as x,
          sum([some other calculation]) as y
   from   tableName
) s

answered Apr 16 '13 at 12:27

John Woo

258,903
69
498
492

Thanks. That's a shame... I was hoping I could do it the lazy way – K_McCormic Apr 16 '13 at 12:35
hehe just choose the best one that fits for you `:D` – John Woo Apr 16 '13 at 12:37

score 1 · Answer 2 · answered Apr 16 '13 at 12:28

1

The SQL standard does not support this. You have to write:

select
sum([some calculation]) as x,
sum([some other calculation]) as y,
sum([some calculation])/sum([some other calculation]) as z
from 
[rest of the sql...]

There may be some RDBMS out there that support your syntax, though.

answered Apr 16 '13 at 12:28

drunken_monkey

1,760
1
12
14

there is none that support `:)` – John Woo Apr 16 '13 at 12:40

Reference something in the select clause SQL

2 Answers2