1

postgreSQL 9.5.6

How can I make this work using the percent_error alias?

SELECT time::date, 
  sum((status='404 NOT FOUND')::int) as not_found,
  sum((status='200 OK' OR status='404 NOT FOUND')::int) as total,
  sum((status='404 NOT FOUND')::int)/sum((status='200 OK' OR status='404 NOT FOUND')::int)::float as percent_error
  from log
  group by time::date
  having sum((status='404 NOT FOUND')::int)/sum((status='200 OK' OR status='404 NOT FOUND')::int)::float > 0.01;

A query like this work:

select species, 
  count(*) as num 
  from animals 
  group by species 
  having num=1;

But not this - exactly the same as the first example above except here the last line is using percent_error alias:

SELECT time::date, 
  sum((status='404 NOT FOUND')::int) as not_found,
  sum((status='200 OK' OR status='404 NOT FOUND')::int) as total,
  sum((status='404 NOT FOUND')::int)/sum((status='200 OK' OR status='404 NOT FOUND')::int)::float as percent_error
  from log
  group by time::date
  having percent_error > 0.01;
2

I think the simplest way is to use a subquery:

SELECT
    time, not_found, total, percent_error
FROM
    (
    SELECT 
        time::date, 
        sum((status='404 NOT FOUND')::int) as not_found,
        sum((status='200 OK' OR status='404 NOT FOUND')::int) as total,
        sum((status='404 NOT FOUND')::int)::float / 
             sum((status='200 OK' OR status='404 NOT FOUND')::int) 
             as percent_error
    FROM 
        log
    GROUP BY 
        time::date
    ) AS s0
WHERE 
    percent_error > 0.01 ;

dbfiddle here

The actual query plan that you get is exactly the same as with your original query; however, the intent is made (in my opinion) much clearer. This is conceptually equivalent to having a WITH (although the implementation in PostgreSQL is quite different). The advantages being: more portable (i.e.: implemented by more database engines), more optimizable by the query planner (in PostgreSQL).


Side note: This query

select species, 
  count(*) as num 
  from animals 
  group by species 
  having num=1;

doesn't work with postgreSQL 9.6 (nor 8.4). You get the following error (the same as with percent_error):

ERROR:  column "num" does not exist
LINE 5:   having num=1;
                 ^

It works with SQLite and MariaDB / MySQL. It doesn't work either with SQL Server and Oracle. I guess the not working is standard SQL; and the working is an extension.

dbfiddle here

2

A "WITH query" (CTE) may be useful for you (see: https://www.postgresql.org/docs/current/static/queries-with.html - read at least the first paragraph ...)

Here's an example, very close to your situation. Notice: date_ is the equivalent of time::date in your code:

with stats as (
  select
    date_ 
  , sum((status='404 NOT FOUND')::int) as not_found
  , sum((status='200 OK' OR status='404 NOT FOUND')::int) as total
  , sum( (status='404 NOT FOUND')::int) / 
         sum((status='200 OK' OR status='404 NOT FOUND')::int)::float  
    as percent_error
  from log
  group by date_
)
select not_found, total, percent_error
from stats
where percent_error > 0.01 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.