How to group by year from a concatenated column-CodePudding

Having a MySQL table as this, where the id is a concatenation of the date with Ymd format (of the moment the row is inserted) with an incremental id.

|     id      |   weight  |
| 20200128001 |    100    |
| 20200601002 |    250    |
| 20201208003 |    300    |
| 20210128001 |    150    |
| 20210601002 |    200    |
| 20211208003 |    350    |

To make a sum of 'weight' by a single year I'm making:

SELECT sum(weight) as weight FROM `table` WHERE id LIKE '2020%';

resulting in this case as:

How can I make it result in a table of weights by year, instead of querying by every single possible year, resulting in this case as:

| date |  weight  |
| 2020 |    650   |
| 2021 |    700   |

CodePudding user response：

Use one of the string processing functions in MySQL like left()

SELECT LEFT(id,4) as Year, SUM(weight) as Weight
FROM `table`
GROUP BY LEFT(id,4)
ORDER BY LEFT(id,4)

And if you want to limit the results to just those 2 years

SELECT LEFT(id,4) as Year, SUM(weight) as Weight
FROM `table`
WHERE LEFT(id,4) IN (2021, 2022)
GROUP BY LEFT(id,4)
ORDER BY LEFT(id,4)