CODEWITHSUNDEEP
mysql
user1419810
user1419810

Reputation: 846

SQL 4 week period in year grouping

I have an SQL table that contains order data by date. I'm trying to combine the data across years in 4 weeks buckets so that I can compare year on year periods. Luckily the table contains year and week number columns so that I can sum the data to show order totals by week number, for example:

By using SELECT order_year, order_week_number, sum(order_total) from f2l4d1a2ta_237_floodlight_order_data_v1 group by order_week_number ORDER BY order_week_number, order_year

I get:

order_year  order_week_number   sum(order_total)
2017    1   96.40879041
2017    2   33.34092216
2017    3   97.79772267
2017    4   28.05668819
2017    5   75.79034382
2017    6   41.59171513
2017    7   3.754344347
2017    8   66.27940579
2016    1   65.81290635
2016    2   71.17703765
2016    3   65.95184929
2016    4   90.42108213
2016    5   44.32837015
2016    6   19.9644766
2016    7   53.46359297
2016    8   7.059479081

However what I'm really after is to see the order total for the 4 week period in the year, i.e.

order_year  4 week period   sum(order_total)
2017    1   255.6041234
2017    2   187.4158091
2016    1   293.3628754
2016    2   124.8159188

Does anyone know how to group data with SQL in this way?

Thanks,

Matt

Upvotes: 0

Views: 408

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35603

Add 3 to the week number then integer divide by 4 (whole number result)

Eg (1+3) DIV 4 = 1, (4+3) DIV 4 = 1

So GROUP BY (weekno + 3) DIV 4

Upvotes: 2

Related Questions