'Grouping sales revenue by region and by quarter in SQL
I have a SQL Server with sales_revenue, region, and Year_Quarters. I want to create a table that shows sales_revenue per region by quarter.
Desired output would like something like this:
| sales_revenue | region | Quarter_Year |
|---|---|---|
| 250 | Midwest | 2015-Q1 |
| 150 | Northeast | 2015-Q1 |
| 100 | South | 2015-Q1 |
So far, I have tried the following, which has only yielded me sales revenue for each region by day, and simply tells me the quarter of the year that the day falls under.
SELECT
sales_revenue,
region,
CASE
WHEN MONTH(date_of_sale) BETWEEN 01 AND 03 THEN convert(char(4), YEAR(date_of_sale)) + '-Q1'
WHEN MONTH(date_of_sale) BETWEEN 04 AND 06 THEN convert(char(4), YEAR(date_of_sale)) + '-Q2'
WHEN MONTH(date_of_sale) BETWEEN 07 AND 09 THEN convert(char(4), YEAR(date_of_sale)) + '-Q3'
WHEN MONTH(date_of_sale) BETWEEN 10 AND 12 THEN convert(char(4), YEAR(date_of_sale)) + '-Q4'
END AS Year_Quarter
FROM dbo.Data$
GROUP BY region, date_of_sale, sales_revenue
ORDER BY Year_Quarter;
Output of the above:
| sales_revenue | region | Quarter_Year |
|---|---|---|
| 200 | Midwest | 2015-Q1 |
| 210 | Midwest | 2015-Q1 |
| 220 | Midwest | 2015-Q1 |
| 190 | Midwest | 2015-Q1 |
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
