# Using :group with #count generating bad SQL in Postgres **URL:** \ **Category:** rubyonrails-talk\ **Created:** [July 23, 2013, 12:36pm UTC](https://discuss.rubyonrails.org/t/using-group-with-count-generating-bad-sql-in-postgres/67482 "2013-07-23T12:36:58Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![Carlos\_Figueiredo](https://avatars.discourse-cdn.com/v4/letter/c/71e660/32.png) [@Carlos\_Figueiredo](https://discuss.rubyonrails.org/u/Carlos_Figueiredo)\ **Post date:** [July 23, 2013, 12:36pm UTC](https://discuss.rubyonrails.org/t/using-group-with-count-generating-bad-sql-in-postgres/67482/1 "2013-07-23T12:36:58Z")
Your query is wrong… You need group all columns you show that arent the result of an aggregate function… So your group would be group(‘titles.\*’) ---
**Author:** ![5T41N5](https://avatars.discourse-cdn.com/v4/letter/5/ba8739/32.png) [@5T41N5](https://discuss.rubyonrails.org/u/5T41N5)\ **Post date:** [July 23, 2013, 4:48pm UTC](https://discuss.rubyonrails.org/t/using-group-with-count-generating-bad-sql-in-postgres/67482/2 "2013-07-23T16:48:44Z")
Can you write the right (no pun intended) query here? Thanks ---
**Author:** ![Carlos\_Figueiredo](https://avatars.discourse-cdn.com/v4/letter/c/71e660/32.png) [@Carlos\_Figueiredo](https://discuss.rubyonrails.org/u/Carlos_Figueiredo)\ **Post date:** [July 23, 2013, 7:11pm UTC](https://discuss.rubyonrails.org/t/using-group-with-count-generating-bad-sql-in-postgres/67482/3 "2013-07-23T19:11:16Z")
def self.order\_by\_number\_of\_comments\_descending select(‘[titles.id](http://titles.id), [titles.name](http://titles.name), titles.whatever, count([comments.id](http://comments.id)) AS comments\_count’). ``` joins(:comments). group('[titles.id](http://titles.id), [titles.name](http://titles.name), titles.whatever'). order('comment_count DESC') ``` end Assuming titles model is like (id, name, whatever) The result query expected is: SELECT COUNT(\*) AS comments\_count, [titles.id](http://titles.id) AS titles\_id, [titles.name](http://titles.name) as titles\_name, titles.whatever as titles\_whatever FROM “titles” INNER JOIN “comments” ON “comments”.“title\_id” = “titles”.“id” GROUP BY [titles.id, titles.name, titles.whatever](http://titles.id) ORDER BY comments\_count DESC ---
**Author:** ![Carlos\_Figueiredo](https://avatars.discourse-cdn.com/v4/letter/c/71e660/32.png) [@Carlos\_Figueiredo](https://discuss.rubyonrails.org/u/Carlos_Figueiredo)\ **Post date:** [July 23, 2013, 7:18pm UTC](https://discuss.rubyonrails.org/t/using-group-with-count-generating-bad-sql-in-postgres/67482/4 "2013-07-23T19:18:02Z")
Sorry… Are there more columns on titles entity? If yes… the problem is that you tried to show columns you weren’t grouping… and on postgresql it blows an exception… but if there is only one column on titles… ([titles.id](http://titles.id)) so, I can’t figure out what’s the problem you mentioned… What were your expected SQL to say that the result were malformed? ---
**Author:** ![5T41N5](https://avatars.discourse-cdn.com/v4/letter/5/ba8739/32.png) [@5T41N5](https://discuss.rubyonrails.org/u/5T41N5)\ **Post date:** [July 24, 2013, 12:28am UTC](https://discuss.rubyonrails.org/t/using-group-with-count-generating-bad-sql-in-postgres/67482/5 "2013-07-24T00:28:12Z")
There are at least 2 problems with how you recommend the query to be written: 1. Why would titles.\* work in regular SQL but not in Rails’ select method? What would be the case if I have 100 columns on the titles table? 2. During grouping, the normal SQL requirement is to group on a column that is on both tables. As far as I know, grouping can’t be done on two tables when they don’t a common column. In your group call, only [titles.id](http://titles.id) would be useful. The rest won’t Did you try running the resulting query? It should throw an exception concerning the GROUP BY ---
**Author:** ![Scott\_Ribe](https://avatars.discourse-cdn.com/v4/letter/s/a183cd/32.png) [@Scott\_Ribe](https://discuss.rubyonrails.org/u/Scott_Ribe)\ **Post date:** [July 24, 2013, 12:36am UTC](https://discuss.rubyonrails.org/t/using-group-with-count-generating-bad-sql-in-postgres/67482/6 "2013-07-24T00:36:02Z")
You seem to be confusing the grouping with the join condition. The join requires 1 (usually, sometimes more) column common to both tables. The grouping must include all columns not used in the aggregate function. ---
**Author:** ![5T41N5](https://avatars.discourse-cdn.com/v4/letter/5/ba8739/32.png) [@5T41N5](https://discuss.rubyonrails.org/u/5T41N5)\ **Post date:** [July 24, 2013, 10:32am UTC](https://discuss.rubyonrails.org/t/using-group-with-count-generating-bad-sql-in-postgres/67482/7 "2013-07-24T10:32:44Z")
I think so, sorry for the misinformation. Thanks for the correction 🙂 ---
**Author:** ![5T41N5](https://avatars.discourse-cdn.com/v4/letter/5/ba8739/32.png) [@5T41N5](https://discuss.rubyonrails.org/u/5T41N5)\ **Post date:** [July 24, 2013, 10:38am UTC](https://discuss.rubyonrails.org/t/using-group-with-count-generating-bad-sql-in-postgres/67482/8 "2013-07-24T10:38:48Z")
@Carlos this query still throws an exception: def self.order\_by\_number\_of\_comments\_descending select(‘[titles.id](http://titles.id), titles.title, titles.submitter\_name, titles.submitter\_email, titles.created\_at, titles.updated\_at, count([comments.id](http://comments.id)) AS comments\_count’). ``` joins(:comments). group('[titles.id](http://titles.id), titles.title, titles.submitter_name, titles.submitter_email, titles.created_at, titles.updated_at'). order('comments_count DESC') end ``` still complaining about not finding comments\_count 😕 I’m using **PostgreSQL 9.1.5** ---
**Author:** ![5T41N5](https://avatars.discourse-cdn.com/v4/letter/5/ba8739/32.png) [@5T41N5](https://discuss.rubyonrails.org/u/5T41N5)\ **Post date:** [July 24, 2013, 10:40am UTC](https://discuss.rubyonrails.org/t/using-group-with-count-generating-bad-sql-in-postgres/67482/9 "2013-07-24T10:40:27Z")
Error message: ```ruby PG::Error: ERROR: column "comments_count" does not exist LINE 1: ...l, titles.created_at, titles.updated_at ORDER BY comments_c... ^ : SELECT COUNT(*) AS count_all, [titles.id](http://titles.id), titles.title, titles.submitter_name, titles.submitter_email, titles.created_at, titles.updated_at AS titles_id_titles_title_titles_submitter_name_titles_submitter_e FROM "titles" INNER JOIN "comments" ON "comments"."title_id" = "titles"."id" GROUP BY [titles.id](http://titles.id), titles.title, titles.submitter_name, titles.submitter_email, titles.created_at, titles.updated_at ORDER BY comments_count DESC ``` ---
**Author:** ![Carlos\_Figueiredo](https://avatars.discourse-cdn.com/v4/letter/c/71e660/32.png) [@Carlos\_Figueiredo](https://discuss.rubyonrails.org/u/Carlos_Figueiredo)\ **Post date:** [July 24, 2013, 11:10am UTC](https://discuss.rubyonrails.org/t/using-group-with-count-generating-bad-sql-in-postgres/67482/10 "2013-07-24T11:10:46Z")
Now I figured out what is the error… I dont know why… But rails changed the column name where you count froum comments\_count to count\_all… And you refer it on your order by statement using the alias you choose… I hope somone more experienced on rails help you fix it, but for now… U could do 2 fix… You can call the alias rails choose (count\_all) on your order statement (ugly way to solve) Or you can call for count(\*) without alias on your order statement (more practiced by dba… Less ugly to solve) ---
**Author:** ![Carlos\_Figueiredo](https://avatars.discourse-cdn.com/v4/letter/c/71e660/32.png) [@Carlos\_Figueiredo](https://discuss.rubyonrails.org/u/Carlos_Figueiredo)\ **Post date:** [July 24, 2013, 11:19am UTC](https://discuss.rubyonrails.org/t/using-group-with-count-generating-bad-sql-in-postgres/67482/11 "2013-07-24T11:19:18Z")
@Yaw, can you test if you change the way you call the count on postgres, rails still change the name of the column? Ex. Try using count([comments.id](http://comments.id)) or count(1) besides count(\*) on your query and let me know what happens… Ps.: The ugly fix of my last email still works