CODEWITHSUNDEEP
sqlpostgresqlsubqueryaggregate-functions
user1301037
user1301037

Reputation: 533

SQL query with percentage calculation from subquery

I have one table: log

+----+---------------------+---------------+
| ID |        Time         |    Status     |
+----+---------------------+---------------+
|  1 | 2016-07-19 03:20:12 | 200 OK        |
|  2 | 2016-07-20 05:20:12 | 404 NOT FOUND |
|  3 | 2016-07-19 00:00:00 | 200 OK        |
|  4 | 2016-07-20 10:20:12 | 404 NOT FOUND |
|  5 | 2016-08-05 07:00:02 | 404 NOT FOUND |
+----+---------------------+---------------+

I need to consolidate the data by "404 NOT FOUND" status group by date order by percentage of total. (bellow)

Desirable outcome

+---------------------+---------+
|        Date         | Errors  |
+---------------------+---------+
| 2016-07-20 00:00:00 | 0.66666 |
| 2016-08-05 00:00:00 | 0.33333 |
+---------------------+---------+

I can't figure out how can I achieve that from just one query. By the time, I started with this query:

SELECT date_trunc('day',time) as "date", count(time) as errors
FROM log
WHERE status = '404 NOT FOUND'
GROUP BY date
ORDER BY errors DESC;

This query result in:

+---------------------+--------+
|        Date         | Errors |
+---------------------+--------+
| 2016-07-20 00:00:00 |      2 |
| 2016-08-05 00:00:00 |      1 |
+---------------------+--------+

Anny ideas or references to achieve the desirable outcome?

Upvotes: 0

Views: 1384

Answers (5)

Hamza Abdaoui
Hamza Abdaoui

Reputation: 2209

To get your desired output, try this query : 

SELECT date_trunc('day',time) as "date", round((
  count(*)::decimal/(
            select count(*) from log WHERE status = '404 NOT FOUND')
),2) as errors
FROM log
WHERE status = '404 NOT FOUND'
GROUP BY date
ORDER BY errors DESC;

This will display : 

date                       errors
2016-07-20T00:00:00.000Z    0.67
2016-08-05T00:00:00.000Z    0.33

This is a working Fiddle

Don't worry about the date format, in my schema I chose a timestamp type

Upvotes: 3

jose_bacoy
jose_bacoy

Reputation: 12684

I hope this will be helpful. I used postgresql9.3

select * 
from (
  with total as (select sum(1) as tot FROM log WHERE status = '404 NOT FOUND')
  SELECT date_trunc('day',time) as "date",
  cast(SUM(CASE WHEN status = '404 NOT FOUND' THEN 1 ELSE 0 END) as decimal) / total.tot as percentage
  FROM log , total
  group by date, total.tot
    ) t
where percentage > 0
ORDER BY percentage desc;

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246198

I think window functions are the most elegant answer:

SELECT DISTINCT
   EXTRACT(day FROM time) AS date,
   CAST(
      count(*) OVER (PARTITION BY CAST(time AS date))
      AS double precision
   ) / count(*) OVER () as errors
FROM log                                                                            
WHERE status = '404 NOT FOUND'
ORDER BY errors DESC;

Upvotes: 1

James
James

Reputation: 3015

Why don't you try with this:

select 
  date_trunc('day',time) as "date", 
  count(time)/(select count(*) from log where status='404 NOT FOUND') as errors
from log
where status = '404 NOT FOUND'
GROUP BY date_trunc('day',time);

Upvotes: -1

DineshDB
DineshDB

Reputation: 6193

Try this Answer.

Hope this helps you:

SELECT date_trunc('day',time) as "date"
    ,COUNT(time)/(SELECT COUNT(*) FROM log WHERE status = '404 NOT FOUND') as errors
FROM log
WHERE status = '404 NOT FOUND'
GROUP BY date
ORDER BY errors DESC;

Upvotes: 0

Related Questions