2

I've got a table which contains event status data, similar to this:

  ID             Time               Status
------  --------------------------  ------
357920  2019-12-25 09:31:38.854764    1
362247  2020-01-02 09:31:42.498483    1
362248  2020-01-02 09:31:46.166916    1
362249  2020-01-02 09:31:47.430933    1
362300  2020-01-03 09:31:46.932333    1
362301  2020-01-03 09:31:47.231288    1

I'd like to construct a query which returns the number of successful events each day, so:

         Time              Count
-------------------------- -----
2019-12-25 00:00:00.000000   1
2020-01-02 00:00:00.000000   3
2020-01-03 00:00:00.000000   2

I've stumbled across this SO answer to a similar question, but the answer there is for all the data returned by the query, whereas I need the sum grouped by date range.

Also, I cannot use BETWEEN to select a specific date range, since this query is for a Grafana dashboard, and the date range is determined by the dashboard's UI. I'm using Postgres for the SQL dialect, in case that matters.

Ergi Nushi
  • 837
  • 1
  • 6
  • 17
Nik Reiman
  • 39,067
  • 29
  • 104
  • 160
  • 1
    Tag your question with the database you are using. – Gordon Linoff Jan 07 '20 at 14:54
  • @GordonLinoff Since the question is about SQL and not related specifically to Postgres, I decided *not* to tag the question with this. But sure, you may have a good point, so I'll add this tag. :) – Nik Reiman Jan 07 '20 at 15:15

2 Answers2

1

You need to remove the time from time component. In most databases, you can do this by converting to a date:

select cast(time as date) as dte, 
       sum(case when status = 1 then 1 else 0 end) as num_successful
from t
group by cast(time as date)
order by dte;

This assumes that 1 means "successful".

The cast() does not work in all databases. Other alternatives are things like trunc(time), date_trunc('day', time), date_trunc(time, day) -- and no doubt many others.

In Postgres, I would phrase this as:

select date_trunc('day', time) as dte, 
       count(*) filter (where status = 1) as num_successful
from t
group by dte
order by dte;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

How about like this:

SELECT date(Time), sum(status) 
FROM table 
GROUP BY date(Time)
ORDER BY min(Time)
Dave Gruenewald
  • 5,329
  • 1
  • 23
  • 35
Turbot
  • 5,095
  • 1
  • 22
  • 30