How can one group by ranges and perform aggregation in mysql?

Question

I have a table as shown below with year and quantity of goods sold in each year, I want to group the year column into ranges of Decades and sum the quantity sold in each decade. Having in mind that the First decade is 1980 - 1989, Second decade is 1981 - 1990, so on... The expected result is also shown in the second table below

sample:                           expected_result:

+------+----------+               +-----------+------------+
| year |   qty    |               |  Decades  | Total_qnty |
+------+----------+               +-----------+------------+
| 1980 |        2 |               | 1980-1989 |         13 |
| 1981 |        1 |               | 1981-1990 |         12 |
| 1983 |        8 |               | 1982-1991 |         12 |
| 1989 |        2 |               | 1983-1992 |         12 |
| 1990 |        1 |               | .         |         .  |
| 1992 |        1 |               | .         |         .  |
| 1994 |        4 |               | .         |         .  |
+------+----------+               +-----------+------------+

Below is the sample code I tried with a couple of others but the result is not as expected,

SELECT t.range AS Decades, SUM(t.qty) as Total_qnty
FROM (
    SELECT case
        when s.Year between 1980 and 1989 then '1980 - 1989'
        when s.Year between 1981 and 1990 then '1981 - 1990'
        when s.Year between 1982 and 1991 then '1982 - 1991'
        when s.Year between 1983 and 1992 then '1983 - 1992'
        else '1993 - above'
        end as range, s.qty
    FROM sample s) t,

group by t.range

I tried this and this but still could not get the expected result. Also I wouldn't want to hardcode things. Please any help will be appreciated.

Please confirm the expected result. Should 3rd decade be '1982-1991' and 4th decade as '1983 - 1992' ? — Samir Selia, Jun 23 '21 at 14:14
@Samir yes please, there is a mistake in the expected_result table above. The 3rd and 4th decades should be '1982 - 1991' and '1983 - 1992' as you correctly mentioned. — Dave K., Jun 24 '21 at 03:18

Dave K. · Answer 1 · 2021-06-24T03:56:58.750

After getting insight from xObert's answer to hank99's question I was able to work around the problem with self join as shown below. Note: The raw table contains the name of product and the year it was sold, with repeated product names and year sold. Which explains why I was able to use COUNT(*) to obtain total number of products sold in each decade. Thank you all!

SELECT year1 ||' - '|| year2 AS Decades, Count_of_qnty
FROM
    (SELECT s1.year year1, s1.year+9 year2, COUNT(*) AS Count_of_qnty
    FROM 
        (SELECT DISTINCT year FROM sample) s1
    JOIN sample s2
         ON s2.year>=year1 AND s2.year <= year2
    GROUP BY year1)

How can one group by ranges and perform aggregation in mysql?

1 Answers1