1

I have this current query:

enter image description here

which results in:

enter image description here

I want to take all of those "status_changes" and break them out uniquely by day, so that I can see the status changes by day, and them sum them up at the end. It would look like this:

enter image description here

Is this possible? My main goal is to make it so line 12 "count(distinct driver_id) as status_changes" is unique by day, instead of throughout the entire "spot_Check_begin" to "spot_check_end" timeframe.

Perhaps I am missing the bigger picture of a possible different query?

Americo
  • 909
  • 5
  • 16
  • 29
  • 1
    I don't know postgresql but see if you can do some kind of pivot... [maybe like this](http://stackoverflow.com/questions/3002499/postgresql-crosstab-query) [or this](http://www.craigkerstiens.com/2013/06/27/Pivoting-in-Postgres/). – gloomy.penguin Nov 08 '13 at 00:24

1 Answers1

1

You can use SUM combined with CASE WHEN instead of COUNT.

For example:

SELECT 
sum(CASE WHEN created_at::DATE = CURRENT_DATE THEN 1 ELSE 0 END) as status_changes_1,
sum(CASE WHEN created_at::DATE = CURRENT_DATE - interval '1 day' THEN 1 ELSE 0 END) as status_changes_2,
sum(CASE WHEN created_at::DATE = CURRENT_DATE - interval '2 day' THEN 1 ELSE 0 END) as status_changes_3,
FROM

In development version we can use FILTER with aggregate functions (http://www.postgresql.org/docs/devel/static/sql-expressions.html#SYNTAX-AGGREGATES). Let's hope we can use it in near future.

count(driver_id) FILTER (WHERE created_at = CURRENT_DATE) AS status_changes_1
gkocjan
  • 735
  • 7
  • 15