CODEWITHSUNDEEP
sql
sds
sds

Reputation: 60014

How do I use a newly created variable in the same select statement?

How do I reuse a variable (column) which I just created in the same select?

create table t2
as select count(bar) as foo, foo * log(foo) as ifoo
from t1
group by bar;

as you can see, I am creating t2.foo (the number of times each value of bar appears in t1) and I want to use that value to compute t2.ifoo.

Upvotes: 3

Views: 3568

Answers (2)

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

One possible approach is this:

create table t2
as
    select foo, foo * log(foo) as ifoo
    from (
        select count(*) as foo, bar
        from t1
    ) q
    group by bar;

I don't know what bar is here, but it doesn't matter, you just need to make sure it exists in the subquery.

Upvotes: 1

Yaakov Ellis
Yaakov Ellis

Reputation: 41520

You need to repeat the same function in the second column

create table t2 as 
  select count(*) as foo, 
         count(*) * log(count(*)) as ifoo
  from t1
  group by bar;

(Note - perhaps you meant to create a view here, or maybe just a query, but this isn't valid syntax for creating a table. The same logic will apply though - you can't use the alias of the column in the same statement to refer to itself, the logic must be repeated).

Upvotes: 1

Related Questions