3 ms·
If I want a table of figures for the past 18 months, I could aggregate them like so: GROUP BY DATEDIFF('month', "transaction_date", NOW()) And get a resul
by function_seven 5y ago
If I want a table of figures for the past 18 months, I could aggregate them like so:
GROUP BY DATEDIFF('month', "transaction_date", NOW())
And get a result set that shows the relative month in one column, and the sum in another.
And those values relative values won't change every month like they would if I just grouped by
EXTRACT('year-month', "transaction_date")
(or whatever the syntax is for that)
Also useful for a stored procedure or view than can then be JOINed in other queries as time goes on.
- ants_a 5y agoIn PostgreSQL you would group by date_trunc('month', transaction_date)
- function_seven 5y agoThat would produce dates instead of integers though. I want a table that looks like this: MonthsAgo | Sales ================== 4 | 4,500 3 | 7,204 2 | 12,578 1 | 15,748 0 | 34,485 And maybe use a parameter so the user can go by week, or quarter, or whatever.