Struggling with Ecto, distinct, and sum

If you are already grouping by id, I don’t think distinct("id") is doing anything for you at all.

If I don’t I get SQL error like:

ERROR: column "stats_daily.id" must appear in the GROUP BY clause or be used in an aggregate function

edit: it seems to return the desired results if I keep the GROUP BY and remove the DISTINCT, so this gives me something new to work with

Can you show the actual ecto query you ran as well as the logged SQL? A key part of my comment was “if you are already grouping”. The error seems to indicate that you aren’t. You need to do something like:

    day = "day"

    from(p in "stats_daily", select: [p.id, sum(p.hits)], limit: ^count)
    |> where(fragment("? = CURRENT_DATE - 1", literal(^day)))
    |> group_by([p], p.id)
    |> order_by([p], desc: sum(p.hits))

This worked.

I had it stuck in my head that I needed DISTINCT because I saw this same change applied in another internal system accessing the same database which modified the query to have both DISTINCT and GROUP BY and the query is working over there.

I should not have just blindly trusted that this was the best solution, but I don’t write SQL all day and it seemed right.

Thank you kindly for your time.

edit: the use of |> order_by([p], desc: sum(p.hits)) ← sum(p.hits) here is really surprising to me as it looks like you’re trying to sum it here as well. I’ll have to research this more thoroughly.

@feld you have to sort by what is in the result of the query eg the select part. You aren’t selecting p.hits you’re selecting sum(p.hits) so that’s what you have to sort on as well. Postgres is smart enough to not do the sum twice.