# Count By Group SQL Syntax

**URL:** <https://www.ruby-forum.com/t/count-by-group-sql-syntax/117558>\
**Category:** Rails\
**Created:** [October 18, 2007, 2:38am UTC](https://www.ruby-forum.com/t/count-by-group-sql-syntax/117558 "2007-10-18T02:38:59Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![nick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/f9ae1b/32.png) [@nick](https://www.ruby-forum.com/u/nick)\
**Post date:** [October 18, 2007, 2:38am UTC](https://www.ruby-forum.com/t/count-by-group-sql-syntax/117558/1 "2007-10-18T02:38:59Z")

</div>

Dear all,

I’m trying to figure out how many rows I have for each group in the  
following query:

```
@trails = Trail.find(:all,
                     :select => "country",
                     :group => "country")

```

How can I change this query to retrieve one column with the “country”  
list and one with a “count” column ?

Thank you,

Nick,

---

<div class="post-metadata">

**Author:** ![nick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/f9ae1b/32.png) [@nick](https://www.ruby-forum.com/u/nick)\
**Post date:** [October 18, 2007, 3:42am UTC](https://www.ruby-forum.com/t/count-by-group-sql-syntax/117558/2 "2007-10-18T03:42:07Z")

</div>

It is illegal in SQL to have count star and another col. So I tkink u  
can’t do it

Sent from my iPhone

---

<div class="post-metadata">

**Author:** ![nick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/f9ae1b/32.png) [@nick](https://www.ruby-forum.com/u/nick)\
**Post date:** [October 18, 2007, 5:22am UTC](https://www.ruby-forum.com/t/count-by-group-sql-syntax/117558/3 "2007-10-18T05:22:03Z")

</div>

The SQL line itself would be:

SELECT count(\*), country FROM trails GROUP BY country;

So, it is perfectly legal.

I don’t know how to do this in Rails though, but I can’t imagine there  
not being a way…

–Michael

---

<div class="post-metadata">

**Author:** ![nick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/f9ae1b/32.png) [@nick](https://www.ruby-forum.com/u/nick)\
**Post date:** [October 19, 2007, 4:38am UTC](https://www.ruby-forum.com/t/count-by-group-sql-syntax/117558/4 "2007-10-19T04:38:28Z")

</div>

That’s it! Thanks!

On Oct 18, 3:53 am, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![nick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/f9ae1b/32.png) [@nick](https://www.ruby-forum.com/u/nick)\
**Post date:** [October 18, 2007, 9:53am UTC](https://www.ruby-forum.com/t/count-by-group-sql-syntax/117558/5 "2007-10-18T09:53:48Z")

</div>

Is Trail.count :all, :group =\> ‘country’ what you want?

Fred