column does not exist error even when using the 'as' keyword

Question

I get this error:

ERROR:  column "errors" does not exist  
LINE 11: where errors >= 1

even that i checked the result with select.

I'm using postgresql server, i have a table named log like the following:

Column |           Type           |                    Modifiers                     
--------+--------------------------+--------------------------------------------------
 path   | text                     | 
 ip     | inet                     | 
 method | text                     | 
 status | text                     | 
 time   | timestamp with time zone | default now()
 id     | integer                  | not null default nextval('log_id_seq'::regclass)

there are lots of lines in the db.

here is my query

select 
    a.date, 
    (cast(a.count as decimal) * 100 / b.count) as errors 
from (
    select date(time) as date, count(status) from log
    where status!='200 OK'
    group by date
    order by date asc
) as a 
join (
    select date(time) as date, count(status) 
    from log
    group by date
    order by date asc
) as b on a.date=b.date
where errors >= 1
order by errors desc;

but when I try this query without the 'where errors>=1', I got the expected result with 2 columns one named date and another named errors

here is the result

select 
    a.date, 
    (cast(a.count as decimal) * 100 / b.count) as errors 
from (
    select date(time) as date, count(status) from log
    where status!='200 OK'
    group by date
    order by date asc
) as a 
join (
    select date(time) as date, count(status) 
    from log
    group by date
    order by date asc
) as b on a.date=b.date
order by errors desc;

Result:

    date    |         errors         
------------+------------------------
 2016-07-17 |     2.2626862468027260
 2016-07-19 | 0.78242171265427079381
 2016-07-24 | 0.78221415607985480944
 2016-07-05 | 0.77493816982687551525
 2016-07-06 | 0.76678716179209113813

You cannot use a column alias in the `where` clause associated with the `select`. You can repeat the expression, use a subquery, CTE, or lateral join. — Gordon Linoff, Oct 13 '19 at 15:20

Barbaros Özhan · Accepted Answer · 2019-10-13T15:27:29.363

replace where errors >= 1 with (cast(a.count as decimal) * 100 / b.count)>=1 since there's no column called errors but a derived column :

select a.date, (cast(a.count as decimal) * 100 / b.count) as errors
  from (select date(time) as date, count(status)
          from log
         where status != '200 OK'
         group by date
         order by date asc) as a
  join (select date(time) as date, count(status)
          from log
         group by date
         order by date asc) as b
    on a.date = b.date
 where (cast(a.count as decimal) * 100 / b.count) >= 1
 order by errors desc;

OR

It may be used like above as below :

select *
  from (select a.date, (cast(a.count as decimal) * 100 / b.count) as errors
          from (select date(time) as date, count(status)
                  from log
                 where status != '200 OK'
                 group by date
                 order by date asc) as a
          join (select date(time) as date, count(status)
                 from log
                group by date
                order by date asc) as b
            on a.date = b.date) q
 where errors >= 1
 order by errors desc;

within a subquery.

Gordon Linoff · Answer 2 · 2019-10-13T18:58:21.463

0

Your query would be more simply written as:

select l.time::date as dte,
       100 * avg( (status <> '200 OK')::int) as error_rate
from (
    from log l
    group by dte
    having 100 * avg( (status <> '200 OK')::int) >= 1
    order by error_rate desc;

You can use aliases in the having clause of many databases, but not Postgres.

edited Oct 13 '19 at 18:58

answered Oct 13 '19 at 15:22

Gordon Linoff

1,242,037
58
646
786

there is a syntax error i believe. – OSEMA TOUATI Oct 13 '19 at 15:29
select l.time::date as dte, 100 * avg( (status <> '200 OK')::int) as error_rate from log l group by dte having error_rate >= 1 order by error_rate desc i tried this but it says that error_rate does not exist? – OSEMA TOUATI Oct 13 '19 at 15:30
1

@OSEMATOUATI . . . My bad. Postgres is one of the databases that *does not* allow such references. – Gordon Linoff Oct 13 '19 at 18:58

score 0 · Answer 3 · answered Oct 13 '19 at 15:22

0

Replace

where errors >= 1

with

where (cast(a.count as decimal) * 100 / b.count) >= 1

You can't use column alias in the same level of your query.

answered Oct 13 '19 at 15:22

Kamil Gosciminski

16,547
8
49
72

column does not exist error even when using the 'as' keyword

3 Answers3

Related