CODEWITHSUNDEEP
sqlpostgresql
hypnotoad
hypnotoad

Reputation: 59

Postgres: Query that returns percentage

My SQL is rusty. I have a table that logs all HTTP requests and their status. What I am wanting to do is create a query that shows the percentage of the results where status='404 NOT FOUND' PER DAY

here is what the table looks like

articles=# select * from log limit 1;

             path              |       ip       | method | status |              time          |   id
-------------------------------+----------------+--------+--------+----------------------------+---------
 /article/balloon-goons-doomed | 198.51.100.108 | GET    | 200 OK |     2016-07-01 06:02:39-05 | 1688046

I was able to get the raw counts like this, but not sure how to get the percentage

SELECT date_trunc('day', time) "day", count(*) as count FROM log WHERE 
status='404 NOT FOUND' group by 1 ORDER BY 1;

Upvotes: 0

Views: 453

Answers (3)

pozs
pozs

Reputation: 36214

What you need is conditional aggregation.

Form PostgreSQL 9.4, there is an explicit syntax (agg_func(...) FILTER (WHERE <predicate>)) for it:

SELECT   date_trunc('day', time) "day",
         COUNT(*) FILTER (WHERE status = '404 NOT FOUND')::NUMERIC
           / COUNT(*) "not found / total"
FROM     log
GROUP BY 1
ORDER BY 1

For earlier versions, you can simulate that with the CASE expression (you just need to make sure, that the CASE expression's THEN branch always be non-NULL; leaving out the ELSE branch will generate NULLs in all the other cases, so COUNT() won't count them):

SELECT   date_trunc('day', time) "day",
         COUNT(CASE WHEN status = '404 NOT FOUND' THEN status END)::NUMERIC
           / COUNT(*) "not found / total"
FROM     log
GROUP BY 1
ORDER BY 1

Upvotes: 2

Bharath K
Bharath K

Reputation: 197

You can try this: The below sample query will give the total record count and also the count of records that are satisfying your condition. (This is MYSQL example)

SELECT
((COUNT * 100)/TOTAL_REC_COUNT) AS PERCENT
FROM
(
SELECT COUNT(*) as TOTAL_REC_COUNT,SUM(CASE WHEN STATUS='404 NOT FOUND' then 1 ELSE 0 END) AS COUNT
FROM test.sample_table
) TMP

Upvotes: 0

Vincentius Kevin
Vincentius Kevin

Reputation: 379

Is it ok to select the total count in a sub query?

SELECT date_trunc('day', time) as day, count(*) / totalCount.count * 100 as percentage
FROM log,
(
    SELECT count(*) FROM log
) totalCount
WHERE status='404 NOT FOUND' group by 1 ORDER BY 1;

Upvotes: 0

Related Questions