CODEWITHSUNDEEP
sqlpostgresqlcolumn-alias
Diego Villafuerte
Diego Villafuerte

Reputation: 69

Column does not exist in psql

I am trying to filter out days with more than 1% error rates for a website. I already have a table that shows the individual days with their respective error rates, but when I try to include a "where" or "having" clause to filter out the days with a ratio below .01, the query stops working and it says my column does not exist, even though I declared it a few characters before. This is the code: 

select date(time) as day, 
    (trunc(cast(count(*) filter (where status similar to '%404%') as decimal) / count(*), 5)) as col1 
    from log
    where col1 > 0.01 
    group by day 
    order by col1 desc;

This is the error i get 

ERROR:  column "col1" does not exist
LINE 4: where col1 > 0.01

Thanks!!

Upvotes: 3

Views: 2491

Answers (2)

Aaron Dietz
Aaron Dietz

Reputation: 10277

The issue is that you can't reference a column alias col1 in a WHERE clause, unless you layer the query.

Repeated condition option:

select date(time) as day, 
       (trunc(cast(count(*) filter (where status similar to '%404%') as decimal) / count(*), 5)) as col1 
from log
group by day 
having (trunc(cast(count(*) filter (where status similar to '%404%') as decimal) / count(*), 5)) > 0.01
order by col1 desc;

Derived table option:

select day, col1
from (select date(time) as day, 
             (trunc(cast(count(*) filter (where status similar to '%404%') as decimal) / count(*), 5)) as col1 
      from log
      group by day) as derivedTable
where col1 > 0.01

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

col1 is the result of an aggregation. Postgres allows aliases for columns in the group by, but not having. So move the condition to a having clause:

select date(time) as day, 
      (trunc(cast(count(*) filter (where status similar to '%404%') as decimal) / count(*), 5)) as col1 
from log
group by day 
having (trunc(cast(count(*) filter (where status similar to '%404%') as decimal) / count(*), 5)) > 0.01 
order by col1 desc;

Although filter is really fancy, I think this version of the logic is simpler:

      trunc(cast(avg( (status similar to '%404%')::decimal), 5) as col1

It is also easier to fit into the having clause.

Upvotes: 3

Related Questions