GROUP BY month and year - SQLite

Felipe Dourado 0
Viewed 53

I got the following SQLite database:

enter image description here

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:

enter image description here

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?

sqlite sql
2 Answers

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.

Tim Biegeleisen0

For SQLite the only valid text-based date format is YYYY-mm-dd.
When you use any other format with date functions like strftime() the result is null and this is the main reason that your code does not work.

Since you mention that in the database the dates have the format YYYY/mm/dd you can update to the correct format:

UPDATE stock_tracking_Negociacao
SET data = REPLACE(data, '/', '-');

and then your query should be:

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