sql to get totals for last six months

Straight to the point on this one

SELECT TOP 6 SUM(gross), MONTH(transactiondate), YEAR(transactiondate) FROM [orders]
GROUP BY MONTH(transactiondate), YEAR(transactiondate)
ORDER BY YEAR(transactiondate) DESC, MONTH(transactiondate) DESC

So we are grouping by month and year, then ordering most recent year first, then most recent months, this means that even if the group of months whose totals we are interested in spans more than one year, we get the months in reverse chronological order, one year at a time.

Post a comment