feld
Struggling with Ecto, distinct, and sum
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
Marked As Solved
benwilson512
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))
Also Liked
feld
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.
benwilson512
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Catch errors, track performance, monitor hosts and more.
Categories:
Sub Categories:
Forums
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Practical resources that improve the lives of professional developers.
Develop your skills with books, videos, and courses.
Enabling companies to succeed by building software people love.
Courses that'll move you from confusion to "Aha, now I get it!"