Is there a way to build a query that returns a record set consisting of a date and the number of records == that date? For example, I have a DB that is tracking inspections that take place on every day. I want to build a data set to be used for a trending graph to see how many inspections took place on each day streching back 365 days.
Record set desired would look like this:
date | InspectionCount 2013-12-30 265 2013-12-29 123 2013-12-28 425 . . . 2013-01-01 15
Is there a way to do that without write code that will simple loop through 365 times and sort of manually generate daily counts like this:
select count(reporting_id) as InspectionCount from inspection where close_conf_date >= '2013-12-30' and close_conf_date < '2013-12-31'
while gradually winding the clock back 365 days? Please let me know if you need more info.
I have just tried this:
WITH RECURSIVE t(n) AS ( VALUES (cast('2013-12-30' as timestamp)) UNION ALL SELECT n-interval '1 day' FROM t WHERE n >=('2013-12-30'-interval '365 day') ) SELECT cast(t.n as date) as date_column, count(reporting_id) FROM t INNER JOIN inspection i ON (cast(t.n as date) = cast(i.close_conf_date as date)) GROUP BY cast(t.n as date)
and I am getting this error i do not really understand: interval field value out of range: "2013-12-30"