Postgres Subquery Group By Date

Question

I have a simple log table and want to calculate an error rate per day. The data looks like this:

SELECT count(date(time)) as counts, date(time) as log_date, status 
FROM log 
GROUP BY log_date, status;

returns

 counts |  log_date  |    status
--------+------------+---------------
  38431 | 2016-07-01 | 200 OK
    274 | 2016-07-01 | 404 NOT FOUND
  54811 | 2016-07-02 | 200 OK
    389 | 2016-07-02 | 404 NOT FOUND

but I want something like

 errors |  log_date  | totals 
--------+------------+--------
    274 | 2016-07-01 |  38705
    389 | 2016-07-02 |  45845
    401 | 2016-07-01 |  54378

I've tried using a subquery, but that gives me a running total instead of a daily total, i.e.

SELECT count(*) as errors, date(e.time) as log_date, totals
FROM log e, (SELECT COUNT(*) as totals FROM log t) AS total_counts
WHERE e.status !='200 OK'
GROUP BY log_date, totals
ORDER BY log_date;

returns

 errors |  log_date  | totals
--------+------------+---------
    274 | 2016-07-01 | 1677735
    389 | 2016-07-02 | 1677735
    401 | 2016-07-03 | 1677735

and I get different errors if I try to use a GROUP BY within the subquery. I know I'm close, but I don't know how to make the outer and inner queries both group on the same day.

Unrelated, but: count(date(time)) is exactly the same thing as count(time). And if time is defined as not null this is exactly the same thing as count(*) — user330315
– user330315, Commented Dec 18, 2017 at 7:03
Good point, I was confusing myself because I need the date(time) for the GROUP BY. — manisha
– manisha, Commented Dec 20, 2017 at 5:52

Paul Maxwell · Accepted Answer · 2017-12-20 07:57:13Z

2

Use a case expression inside the aggregate function ("conditional aggregates")

SELECT
       date(time) as log_date
     , count(case when status <> '200 OK' then 1 end) as errors
     , count(*) as totals
FROM log
GROUP BY
       date(time)

edited Dec 20, 2017 at 7:57

answered Dec 18, 2017 at 6:12

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

Or use the filter clause: count(*) filter (where status <> '200 OK')

manisha Over a year ago

that indeed works. i thought I tried something like that, but it didn't work for me. obviously I did something different. only think to change is you need an as in date(time) log_date. becomes date(time) as log_date. thanks!

Collectives™ on Stack Overflow

Postgres Subquery Group By Date

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related