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
Trending in Questions
Other Trending Topics
Latest on Elixir Forum
Sponsor Spotlight
Deploy Phoenix to your own VPS with PaaS ease: push-to-deploy, zero downtime, auto SSL, managed Postgres. 14-day free trial at potions.io
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #elixirconf-us
- #websockets
- #supervisor
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #metaprogramming
- #performance
- #security
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.
Error tracking, logs, uptime, and Just Enough APM. Built for developers.
Practical resources that improve the lives of professional developers.
Producing high quality Elixir screencasts since 2017.
Enabling companies to succeed by building software people love.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"
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:
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
@feld you have to sort by what is in the result of the query eg the
selectpart. You aren’t selectingp.hitsyou’re selectingsum(p.hits)so that’s what you have to sort on as well. Postgres is smart enough to not do thesumtwice.