CODEWITHSUNDEEP
mysql
Vlad
Vlad

Reputation: 1919

Group Date Bi-Weekly

I am trying to sum subtotals in my tablet group bi-weekly. There are a lot of answers on how to filter last bi-weekly interval, but so far no working solution on how to properly do the grouping.

Table Columns: order_date, subtotal

I've had success with grouping weekly, but no idea how to do it biweekly

SELECT CONCAT(YEAR(order_date), '/', WEEK(order_date)) AS week_name, SUM(subtotal)
FROM m_orders

Upvotes: 1

Views: 2162

Answers (1)

tpdi
tpdi

Reputation: 35171

Append group by WEEK(order_date) DIV 2 to your query:

SELECT 
 CONCAT(YEAR(order_date), '/', WEEK(order_date) DIV 2) AS fortnight_number, 
 SUM(subtotal)
FROM m_orders
group by YEAR(order_date), WEEK(order_date) DIV 2

There are some issues around start of year and end of year, and the different arguments to MySql's week function determine both the range returned (0-53 or 1-53) and how the first week of the year is determined, but this is the basic idea.

(Depending on business requirements, you probably want week(order_date, 1) / 2. The second argument, 1, specifies that weeks start on Mondays, range from 0-53, and the first week of the year is the first week with three or more days in that year. But check with the business, of course.)

Edit: corrected / to DIV

Upvotes: 1

Related Questions