how can I count by week, month, and YTD with postgres?

Question

under Postgres 9.3 I am trying to run a query to summarize some data from table a:

| Entry date-Date | Customer ID-charvar | received-Boolean | Processed-Boolean | finalized-Boolean | billed-Date |

I would like use count(case when) to count all the records by the current week, month, then all of time into table b:

|report date | Total Received Weekly | Received Monthly | Received all of time | so on...

I am having a difficult time wrapping my head around how to write that query. I have thought about extracting the week day and month number to determine my records to sum for each length of time but then how would I make it repeatable without having to change the dates so I could automate it to run weekly through either PgAgent or CRON?

Answer 1

Too long for a comment now: I don't speak PGSQL so try adapting the following for your date extraction functions:

SELECT
 MAX(CurrentWeekCount) As CurrentWeekCount
 ,MAX(CurrentMonthCount) As CurrentMonthCount
 ,MAX(AllCount) As AllCount
FROM
(

count all the records by the current week

 SELECT
 COUNT(CustomerID) As CurrentWeekCount
 ,NULL As CurrentMonthCount
 ,NULL As AllCount
 FROM YourTable
 WHERE DATEPART(week, YourDateColumn) = DATEPART(week, GETDATE())
 UNION ALL

month

SELECT
 NULL As CurrentWeekCount
 ,COUNT(CustomerID) As CurrentMonthCount
 ,NULL As AllCount
 FROM YourTable
 WHERE DATEPART(month, YourDateColumn) = DATEPART(month, GETDATE())
 UNION ALL

all of time

 SELECT
 NULL As CurrentWeekCount
 ,NULL As CurrentMonthCount
 ,COUNT(CustomerID) As AllCount
 FROM YourTable
) x