CODEWITHSUNDEEP
sqlpostgresql
Fezofchekov
Fezofchekov

Reputation: 69

How to calculate percentage in SQL

So, I have this database containing some online articles, their titles, their authors, and a log history that contains how many people logged into the website to read which article and when.

The data base has 3 tables Authors table, with columns: names, bio, ID(author's id)

Articles table with columns: author (has the values of ID from table authors), title, slug, lead, time, body, id(article's id)

Log table, with columns: Path, IP, method, status, time, ID(user's id)

It is required to determine on which days did more than 1% of requests lead to errors? This is determined through columns: time and status from table log, the status column contains either the value: OK to indicate that the web page loaded successfully, or the value: 404 not found to indicate an unsuccessful log.

I tried coming up with the following solution: 

select time::DATE, 100.*count(status)/(select count(*) from log) as error 
from log 
where log.status ='404 NOT FOUND' 
group by time::DATE;

but it gave me a table full of zeros. Can anyone help me calculate the right percentage?

Upvotes: 1

Views: 137

Answers (4)

Lucas
Lucas

Reputation: 630

If you are using a version greater than 9.4 this could be done using the FILTER clause, just remember to cast your counts to a float or numeric ::real, ::double precision, ::numeric

SELECT
    time::date,
    count(*) as total,
    (
        count(status) filter (where log.status = '404 NOT FOUND') /
        count(*)::real
    ) * 100 as error
FROM log
GROUP BY time::date
HAVING count(*) > 0

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269793

I would write this as:

select time::DATE,
       avg( (status = '404 NOT FOUND')::int ) as not_found_ratio
group by time::DATE
having not_found_ratio > 0.01;

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

You can try to use conditional aggregation in a HAVING clause.

SELECT time::date,
       count(CASE
               WHEN status = '404 NOT FOUND' THEN
                 1
             END)::decimal
       / count(*)
       * 100 error
       FROM log
       GROUP BY time::date
       HAVING count(CASE
                      WHEN status = '404 NOT FOUND' THEN
                        1
                    END)::decimal
              / count(*)
              * 100
              > 1;

Upvotes: 1

Steven Harrison
Steven Harrison

Reputation: 31

Looks like you need to cast the COUNT() functions as DECIMAL. COUNT() returns as INT and therefore won't have the decimal component necessary to calculate the percentage.

Upvotes: 0

Related Questions