Sum by month

Questions / Help
1

Hi,

i´ve

pre_query
                                |> order_by([a,p],  fragment("to_char(?,'MM')", a.inserted_at))
                                |> group_by([a,p], [fragment("to_char(?,'MM')", a.inserted_at),fragment("to_char(?,'Mon')", a.inserted_at) ])
                                |>select([a,p],[fragment("to_char(?,'Mon')", a.inserted_at),count(a.id)])
                                |> Repo.all

this gives me

  ["Aug", 1],
  ["Aug", 1],
  ["Aug", 1],
  ["Aug", 1],
  ["Sep", 1],
  ["Sep", 1]

I´d like to have
["Aug", 4], ["Sep", 2]

I´ve tried with sum. But you can´t sum an uuid.

Please help.

2

this gives me

[“Aug”, 1],
[“Aug”, 1],
[“Aug”, 1],
[“Aug”, 1],
[“Sep”, 1],
[“Sep”, 1]

I´d like to have
[“Aug”, 4], [“Sep”, 2]

Maybe post-process that result?, e.g.

iex(37)> things
[["Aug", 1], ["Aug", 1], ["Aug", 1], ["Aug", 1], ["Sep", 1], ["Sep", 1]]
iex(38)> things |> Enum.group_by(fn(x) -> x end) |> Enum.map(fn(x) ->
[List.first(elem(x,0)), Enum.count(elem(x,1))] end)
[["Aug", 4], ["Sep", 2]]
iex(39)>
3

The SQL I’d use is…

SELECT
date_trunc('month', inserted_at) as year_month,
count(t.id)
FROM table t
GROUP BY year_month
ORDER BY year_month;

I’ve got more SQL under my belt than Ecto, so I’m not sure if this is idiomatic, but it worked to get similar results for me:

query =
  from t in Table,
  select: [fragment("date_trunc('month', ?) as year_month", t.inserted_at), count(t.id)],
  order_by: fragment("year_month"),
  group_by: fragment("year_month")

The only difference between my results and yours is that date_trunc returns “rounded-down” DateTime tuples. Instead of “February” you get {{2018, 1, 1}, {0, 0, 0, 0}} which is February 1st at 12 AM. I make that choice personally because Year-Month is more interesting to my business logic than just Month, and DateTime tuples are more interesting than strings, but you can modify using your to_char if you like…

query =
  from t in Table,
  select: [fragment("to_char(?, 'Mon') as month", t.created_at), count(t.id)],
  order_by: fragment("month"),
  group_by: fragment("month")

Hope this helps! :slight_smile:

6 Likes
4

@hassan thank you, but i´m looking for a database solution

@landric Seems the right direction. Thank you. However the results seem to be ordered by the months name and not the actual date.

I get:

  ["Aug", 19],
  ["Dec", 76],
  ["Feb", 13],
  ["Jan", 8],
  ["Nov", 20],
  ["Oct", 51],
  ["Sep", 65]

I need

  ["Feb", 13],
  ["Jan", 8],
  ["Dec", 20],
  ["Nov", 51],
  ["Sep", 65]
...

Any idea?

5

It’s ordering by what you tell it… so maybe what you want is:

order_by: fragment("date_trunc('month', ?)", t.created_at),
group_by: fragment("date_trunc('month', ?)", t.created_at)
1 Like
6

This gives an error cause created_at is not in the group_by clause. When i add t.created_at to the group_by i´m where i started. The entries are not summarized. → ["Nov", 1], ["Nov", 1], ["Jan", 1]

Doesn´t work:
group_by: [fragment("date_trunc('month', ?)", t.inserted_at),t.inserted_at]

7

I understand why you wouldn’t want months to be ordered alphabetically, though I’m not sure what your desired ordering is…? February before January? November before September?

I’m going to answer as though you want things grouped by month (not year-month) and in month order. You can correct me from there. :slight_smile:

If you look at the documentation for Postgres (which, as I cite it, you never mentioned explicitly Postgres, but… :man_shrugging:t2: ), you see that there’s also an option to get “month number (01-12)” by using ‘MM’. If you want to order by month number, but still get month name, you could do…

query =
  from t in Table,
  select: [fragment("to_char(?, 'Mon') as month", t.inserted_at), fragment("to_char(?, 'MM') as month_order", t.inserted_at), count(t.id)],
  order_by: fragment("month_order"),
  group_by: [fragment("month_order"), fragment("month")]

The data I get from this query (on my own dataset) looks like…

[
  ["Jan", "01", 92],
  ["Feb", "02", 68],
  ...
  ["Nov", "11", 14],
  ["Dec", "12", 11]
]

I realize you’re not particularly interested in month number, but I believe if you’re going to sort by it, you have to select it. If you really want to get fancy, you can use Enum functions to reformat those results in some way.

1 Like
8

Truly appreciate your help here.

Yes, using postgres :blush:

I (highcharts.js) can only use [month, number]. Not a third option. I´d like it to be ordered desc from the current month. So February, January, December …

chart.PNG1302×466 2.89 KB

9

You could probably get “ordered desc by the current month” by using Postgres’s date_part and now functions in place of my month_order bit, but even as I tried to do the logic now, it really seemed like I’d rather do that in Elixir afterwards. Cramming a giant string of boolean logic, addition/subtraction, and absolute values (maybe?) into a fragment feels unwieldy, and also harder to write tests for.

For both this and for how to turn a list of three items into a list of two items, I’d go take a look at the Elixir documentation for Enum and the documentation for List… You’ll need to get fluent in those anyway.

My guess is your code will look something like…

query = ...what we've got above...

Repo.all(query)
|> Enum.map(...change the "month order" value into "distance from current month"...)
|> Enum.sort_by(...based on the new value above...)
|> Enum.map(...remove the second list item now that things are in the correct order...)

(I’m not sure if taking this approach would be slower than having the database do the work, but your list is never longer than 12 items long. I suspect it’s not a big deal…)

Let me know what you come up with, and if you need any unsticking!

10

You can do something like this:

yourlist = [....]
previous_months = Date.utc_today.month - 1

Enum.drop(yourlist, previous_months)
|> Enum.concat(Enum.take(your list, previous_months))
1 Like
11

Thanks, but i get

  ["Feb", "02", 26],
  ["Aug", "08", 19],
  ["Sep", "09", 65],
  ["Oct", "10", 51],
  ["Nov", "11", 20],
  ["Dec", "12", 76],
  ["Jan", "01", 8]

It should be

  ["Feb", "02", 26],
  ["Jan", "01", 8]
  ["Dec", "12", 76],
  ["Nov", "11", 20],

…

12

It worked easier than i thought :slight_smile:

pre_query
             |> order_by([a,p],  fragment("year_month"))
             |> group_by([a,p],  [fragment("year_month"),fragment("month")])
                                |>select([a,p],[fragment("to_char(?, 'Mon') as month", a.inserted_at),fragment("date_trunc('month', ?) as year_month", a.inserted_at),count(a.id)])
                                |> Repo.all
                                |> Enum.map(fn(monthly_result) -> List.delete_at(monthly_result, 1) end)

Thank you all for your suggestions!

4 Likes
13

I would rather respond with year-months in ISO8601 and leave the presentation part to the JavaScript. Anyway, whether to show the year-months as

Nov, Dec, Jan, Feb, …

or

November 2017, December 2017, January 2018, February 2018

or

平成２９年１１月、平成２９年１２月、平成３０年１月、平成３０年２月

is something the presentation layer should consider, and JavaScript can do this very well thanks to moment.js.

2 Likes