I've got table with three columns: place_id, date, value. For each place for each date there is value, for example
place_id: 1, date: '2014-01-01', value: 1
place_id: 2, date: '2014-01-01', value: 2
place_id: 1, date: '2014-02-02', value: 3
place_id: 1, date: '2014-02-03', value: 4
place_id: 2, date: '2014-02-15', value: 5
My goal is to find for each month sum of max value for all places.
In the context of data above it should looks like (don't matter how date must be showed like - as two columns year and month or one column 'yyyy-MM-01'):
date: '2014-01-01', sum_of_max: 3
date: '2014-02-01', sum_of_max: 9
As I got, I have to use group by twice - firstly to get maximum value for month and place, secondly to summarize maximums I got on first step. But what way let me do it with best performance?
P.S. If it's matter, I'm using PostgreSql 9.2
I don't see good alternatives to using a subquery. The Postgres
datetrunc() function can help with grouping values from your base table by month, as it seems you want to do.
SELECT month, SUM(max_value) AS sum_of_max FROM ( SELECT place_id, date_trunc('month', date) AS month, MAX(value) AS max_value FROM my_table GROUP BY place_id, month ) mx GROUP BY month