Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sum column value and group dates by month with Postgres

Tags:

sql

postgresql

I've got a table in my Postgres DB that looks something like this:

date          duration
2018-05-10      10
2018-05-12      15
2018-06-01      10
2018-06-02      20
2019-01-01      5
2019-01-02      15
2019-04-01      10

And I wish to sum the values for each month and group them by year, month and, month number into something like this:

year    month    month_number   monthly_sum
2018    May         5              25
2018    June        6              30
2019    Jan         1              20
2019    Apr         4              10

And ended up with a query that looks like this:

SELECT 
  to_char(date_trunc('month', date), 'YYYY') AS year,
  to_char(date_trunc('month', date), 'Mon') AS month,
  to_char(date_trunc('month', date), 'MM') AS month_number,
  sum(duration) AS monthly_sum
FROM timesheet 
GROUP BY year, month, month_number

And it works just fine, my question is: is this query considered bad? Will it affect performance if I have like.. 100k rows? I heard using to_char is inferior to date_trunc, which is what I tried to avoid here, I just wrapped the date_trunc in a to_char. Also, having three values in a GROUP BY clause, does it affect anything?

like image 395
erikvm Avatar asked Sep 07 '25 07:09

erikvm


1 Answers

The query is not bad, but you can simplify it.

SELECT to_char(date_trunc('month', date), 'YYYY') AS year,
       to_char(date_trunc('month', date), 'Mon') AS month,
       to_char(date_trunc('month', date), 'MM') AS month_number,
       sum(duration) AS monthly_sum
FROM timesheet 
GROUP BY date_trunc('month', date);

From a performance perspective, shorter GROUP BY keys would have a small impact on performance, but that is not something I would worry about.

like image 188
Gordon Linoff Avatar answered Sep 10 '25 09:09

Gordon Linoff



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!