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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With