CODEWITHSUNDEEP
mysqlsqlstringsumaggregate-functions
Rahul Singh
Rahul Singh

Reputation: 710

MYSQL GROUP BY pair of values with specific column

I have a period column in a table with values in the format Year-SemiQuarter. Example 2016-BQ1, 2016-BQ2, 2016-BQ3.......2016-BQ8, where BQ1 and BQ2 would together make 1stQuarter for the respective year. SImilar for others

Now, I want to group the result by

Quarter

My table is something like this. Have splitted the period into two columns.


+====================+=============+================+==================================+=====+======================================+
| 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                                       |
+--------------------+-------------+----------------+----------------------------------+-----+--------------------------------------+

The output should be :

+====================+==+
| totalNumberOfUnits |  |
+====================+==+
|               2368 |  |
+--------------------+--+
|               2066 |  |
+--------------------+--+
|                898 |  |
+--------------------+--+
|               5330 |  |
+--------------------+--+

So totalNumberOfUnits for 1st quarter for year 2018 is 2368 which is the sum of BQ1 and BQ2. Similar is for others. There is no data for BQ6, so the totalNumberOfUnits would be 898, which the total number of units for semi-quarter BQ5

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!!

The MySQL version I am using is 5.7.26

Upvotes: 0

Views: 189

Answers (2)

ysth
ysth

Reputation: 98398

GROUP BY SUBSTRING_INDEX(rsh.period, '-', 1 ), (SUBSTRING_INDEX(rsh.period, 'Q', -1) + 1) DIV 2

Upvotes: 1

GMB
GMB

Reputation: 222632

You can use string functions and aggregation:

select 
    left(period, 4) as yyyy, 
    floor((right(rsh.period, 1) + 1) / 2) as q,
    sum(totalNumberOfUnits) as totalNumberOfUnits
from mytable
group by yyyy, q

Upvotes: 1

Related Questions