CODEWITHSUNDEEP
sqlite
user14483403
user14483403

Reputation:

Group BY timestamp sqlite3

I have a column having a timestamp like below. I want to group by according to month. I am using the query below. but in a large dataset, it sometimes gives the wrong result. I want to say is that any other way to use group by (per month) in that timestamp?

 SELECT count(distinct a) from abc GROUP BY (select strftime('%m', timestamp) as valMonth) where col1 like '%a%'

timestamp              a      co1
2020-01-23 17:22:57    12     a    
2020-01-23 17:26:59    12     a
2020-02-23 17:26:59    13     b
2020-02-23 17:26:59    13     b
2020-03-23 17:26:59    14     a
2020-03-23 17:26:59    14     a

Upvotes: 1

Views: 63

Answers (1)

forpas
forpas

Reputation: 164089

If you use:

strftime('%m', timestamp)

then you group by month only, so the result would be for example 1 group for all Decembers of all years.
You should group by year and month with:

strftime('%Y-%m', timestamp)

So change your query to this:

SELECT strftime('%Y-%m', timestamp) AS YearMonth,
       ............... 
FROM abc 
GROUP BY YearMonth

Upvotes: 2

Related Questions