Ecto query that does a group_by MONTH on a datetime field and returns list of tuples

Question

As a tag-along question to this question:

How would you alter the query to return a list of tuples with the integer of the month and the grouped count? So your result would look like:

[{1, 2}, {2, 2}, {3, 2}, {4,2}]

Answer 1

Just porting this from the comments on the question.

query 
|> group_by([e], fragment("date_part('month', ?)", e.inserted_at)) 
|> select([e], {fragment("date_part('month', ?)", e.inserted_at), count(e.id)}) 
|> Repo.all