Ruby Postgresl group statement

Question

I have the following postgresql statement which works ok in navicat for Postgresql,

SELECT date_trunc('day', updated_at), count(*) as total_count 
FROM "persons"
WHERE ("persons"."updated_at" BETWEEN '2012-10-17 00:00:00.000000' AND '2012-11-07   12:25:04.082224') 
GROUP BY date_trunc('day',updated_at)
ORDER BY count(updated_at) DESC

I get expected results like

2012-10-31 00:00:00,5
2012-11-06 00:00:00,2
2012-11-05 00:00:00,1

I need to convert this to ruby for a RoR project

I've written this statement as follows

persons = where(updated_at: start.beginning_of_day..Time.zone.now)
persons = persons.group("date(updated_at)")
persons = persons.select("updated_at, count(*) as total_count")
persons = persons.order("count(updated_at)")

however i get the following error

PG::Error: ERROR:  column "persons.updated_at" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT updated_at, count(*) as total_count FROM "persons"  WH...
           ^
: SELECT updated_at, count(*) as total_count FROM "persons"  WHERE ("persons"."updated_at"     BETWEEN '2012-10-17 00:00:00.000000' AND '2012-11-07 14:38:54.674684') GROUP BY     date(persons.updated_at) ORDER BY count(updated_at)

any ideas on what i'm doing wrong?

Purple Hexagon · Accepted Answer · 2012-11-07 15:04:30Z

1

Perhaps you could try

persons = where(updated_at: start.beginning_of_day..Time.zone.now)
persons = persons.group("date(updated_at), updated_at")
persons = persons.select("updated_at, count(*) as total_count")
persons = persons.order("count(updated_at)")

or

persons = where(updated_at: start.beginning_of_day..Time.zone.now)
persons = persons.group("date(updated_at)")
persons = persons.select("date(updated_at), count(*) as total_count")
persons = persons.order("count(updated_at)")

or

persons = persons.find(:all, 
                       :select => "updated_at, count(*) as total_count",
                       :group => "updated_at",
                       :order => "count(updated_at)")

answered Nov 7, 2012 at 15:04

Purple Hexagon

3,5682 gold badges27 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Chris Baxter Over a year ago

I've used the middle option you specified with some success but now I'm getting another error. ActiveModel::MissingAttributeError: missing attribute: status. status is an attribute on my Person class.

Chris Baxter Over a year ago

I've also tried to refine the query to, passes = where(updated_at: start.beginning_of_day..Time.zone.now) passes = passes.group("date(updated_at)") passes = passes.select("date(updated_at), count(*) as total_count"). but now i get (Object doesn't support #inspect)

Chris Baxter Over a year ago

sorry that should be persons not passes

Purple Hexagon Over a year ago

hmm perhaps you could post the code for the person model. As I would think this would have something to do with the model code

Philip Hallstrom · Accepted Answer · 2012-11-07 16:56:13Z

1

You are selecting updated_at, but you are grouping on date(updated_at). You either need to group on updated_at (which probably doesn't make any sense) or change your select so that you are selecting date(updated_at).

In other words, take Jon Day's second example.

answered Nov 7, 2012 at 16:56

Philip Hallstrom

19.9k2 gold badges44 silver badges47 bronze badges

Collectives™ on Stack Overflow

Ruby Postgresl group statement

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related