GROUP BY month and year - SQLite-CodePudding

I got the following SQLite database:

What I'm trying to do is: sum all values based on quantidade grouped by Month/Year (e.g.: 2022/08)

The result I'm expecting grouped by year/month:

My SQL code:

SELECT 
        data, SUM(quantidade) AS sum
        
        FROM stock_tracking_Negociacao
        WHERE mercado = 'Futuro'
        GROUP BY strftime('%Y', data), strftime('%m', data)

Any help?

CodePudding user response：

We can use SUBSTR() here to isolate the month and year, then aggregate:

SELECT SUBSTR(data, 4, 7) AS ym, SUM(quantidade) AS sum
FROM stock_tracking_Negociacao
WHERE mercado = 'Futuro'
GROUP BY 1;

Note that SQLite does not have a formal date type, but rather stores dates as strings.