Struggling with Ecto, distinct, and sum

Questions / Help
,
1

Trying my hardest to reproduce this query with Ecto, but struggling:

SELECT DISTINCT("id"), sum("hits") AS hits
FROM "stats_daily"
WHERE ("day" = CURRENT_DATE - 1)
GROUP BY "id"
ORDER BY "hits"
DESC LIMIT 1000

I don’t believe I am meant to use an AS for the sum(“hits”) when using Ecto or I have not been able to find a working variant that uses as

If I use distinct, it becomes DISTINCT ON which is not what I want and it also modifies ORDER BY. I’ve tried to use fragments to work around that, but then ORDER BY is failing because “hits” doesn’t match.

the Ecto query before applying DISTINCT and SUM was like this:

    day = "day"

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

Any tips would be greatly appreciated

2

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

3

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

4

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))
3 Likes
5

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. :upside_down_face:

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.

1 Like
6

@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.

1 Like