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:
650
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)