title: MYSQL GROUP BY pair of values with specific column
body: <p>I have a period column in a table with values in the format <code>Year-SemiQuarter</code>. Example <strong>2016-BQ1</strong>, <strong>2016-BQ2</strong>, <strong>2016-BQ3</strong>.......<strong>2016-BQ8</strong>, where <strong>BQ1</strong> and <strong>BQ2</strong> would together make 1stQuarter for the respective year. SImilar for others</p>
  <p>Now, I want to group the result by</p>
  <p><strong>Quarter</strong></p>
  <p>My table is something like this. Have splitted the period into two columns.</p>
  <pre><code>
  +====================+=============+================+==================================+=====+======================================+
  | totalNumberOfUnits | productType | productSubType | SUBSTRING_INDEX(rsh.period, '-', 1  )  | SUBSTRING_INDEX(rsh.period, '-', -1) |
  +====================+=============+================+==================================+=====+======================================+
  |               1084 | Apartment   | High Rise      |                             2018       | BQ1                                  |
  +--------------------+-------------+----------------+----------------------------------+-----+--------------------------------------+
  |               1284 | Apartment   | High Rise      |                             2018       | BQ2                                      |
  +--------------------+-------------+----------------+----------------------------------+-----+--------------------------------------+
  |               1883 | Apartment   | High Rise      |                             2018       | BQ3                                       |
  +--------------------+-------------+----------------+----------------------------------+-----+--------------------------------------+
  |                183 | Apartment   | High Rise      |                             2018       | BQ4                                      |
  +--------------------+-------------+----------------+----------------------------------+-----+--------------------------------------+
  |                898 | Apartment   | High Rise      |                             2018       | BQ5                                       |
  +--------------------+-------------+----------------+----------------------------------+-----+--------------------------------------+
  |               2377 | Apartment   | High Rise      |                             2018       | BQ7                                       |
  +--------------------+-------------+----------------+----------------------------------+-----+--------------------------------------+
  |               2953 | Apartment   | High Rise      |                             2018       | BQ8                                       |
  +--------------------+-------------+----------------+----------------------------------+-----+--------------------------------------+
  
  </code></pre>
  <p>The output should be :</p>
  <pre><code>+====================+==+
  | totalNumberOfUnits |  |
  +====================+==+
  |               2368 |  |
  +--------------------+--+
  |               2066 |  |
  +--------------------+--+
  |                898 |  |
  +--------------------+--+
  |               5330 |  |
  +--------------------+--+
  </code></pre>
  <p>So <code>totalNumberOfUnits</code> for 1st quarter for year 2018 is <strong>2368</strong> which is the sum of <strong>BQ1 and BQ2</strong>. Similar is for others. There is no data for BQ6, so the <code>totalNumberOfUnits</code> would be <strong>898</strong>, which the total number of units for <code>semi-quarter</code> <em>BQ5</em></p>
  <p>It could be very trivial but I am not sure how to do that. Couldn't find the same use case in Stack Overflow. Thanks for the help!!</p>
  <p>The MySQL version I am using is <strong>5.7.26</strong></p>