title: How to aggregate MySQL table data by half-year?
body: <p>Can someone help me write a MySQL query in for dividing the data based on Half-Yearly having records having column in Date format?</p>
  
  <p>I wrote the Query for GROUP by Monthly and Quarterly but Half-Yearly is not happening.</p>
  
  <p>I used the following query for weekly, monthly and quarterly:</p>
  
  <pre><code>CREATE view darren_inventory_mrp_forecast_summary as
      ( SELECT row_number() OVER(ORDER BY sub.product_id) as id,
               SUM(sub.quantity)/12 as quantity,
               SUM(sub2.quantity)/4 as quarterly,
               SUM(sub3.quantity)/51 as weekly,
               sub.product_id as product_id FROM
            (SELECT row_number() OVER(ORDER BY product_id) as id,
                    product_id as product_id,
                    SUM(product_qty) as quantity
            FROM stock_move
            WHERE consumed_for IS NOT NULL
            GROUP BY to_char(DATE(create_date), 'YYYY-MM'), product_id 
            ) 
        as sub 
        LEFT JOIN 
            (SELECT row_number() OVER(ORDER BY product_id) as id,
                    product_id as product_id,
                    SUM(product_qty) as quantity
            FROM stock_move
            WHERE consumed_for IS NOT NULL
            GROUP BY to_char(DATE(create_date), 'YYYY-Q'), product_id 
            ) 
        as sub2 on (sub2.product_id=sub.product_id)  
        LEFT JOIN
            (SELECT row_number() OVER(ORDER BY product_id) as id,
                    product_id as product_id,
                    SUM(product_qty) as quantity
            FROM stock_move
            WHERE consumed_for IS NOT NULL
            GROUP BY to_char(DATE(create_date), 'YYYY-WW'), product_id 
            ) 
        as sub3 on (sub3.product_id=sub.product_id)  
        GROUP BY sub.product_id
      )
  </code></pre>