# Best way to generate sums of groups **URL:** \ **Category:** Rails\ **Created:** [June 22, 2006, 9:44pm UTC](https://www.ruby-forum.com/t/best-way-to-generate-sums-of-groups/64184 "2006-06-22T21:44:21Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![JDJDJD](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e36b37/32.png) [@JDJDJD](https://www.ruby-forum.com/u/JDJDJD)\ **Post date:** [June 22, 2006, 9:44pm UTC](https://www.ruby-forum.com/t/best-way-to-generate-sums-of-groups/64184/1 "2006-06-22T21:44:21Z")
I’m a rails newby, so I’m trying to wrap my head around how to tackle this, since my first few attempts failed. Let’s say your database looks like this: Users id, name, created\_at Points id, user\_id, value, created\_at Users complete different tasks and are awarded varying levels of points, which are recorded in the Points table. What if you wanted to display a “high score list”, showing the users in order by who had the most points? It might look like: Username A - 20 points Username B - 14 points Username C - 13 points and so on… You’d need to sum the values for each user\_id, then display them in order. And, based on the user\_ids, you’d need to pull in and display the users’ names from the Users table. I’ve been attempting to do this using various incarnations of Point.sum(:values, :group =\> ‘user\_id’), but without luck. Using that approach, I can display the point totals for each user\_id, but the thing breaks down when I try to bring in the names from the Users table. Any help would be hugely appreciated! ---
**Author:** ![JDJDJD](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e36b37/32.png) [@JDJDJD](https://www.ruby-forum.com/u/JDJDJD)\ **Post date:** [June 22, 2006, 11:43pm UTC](https://www.ruby-forum.com/t/best-way-to-generate-sums-of-groups/64184/2 "2006-06-22T23:43:50Z")
Hey, The problem you are running in to is more of a SQL problem than it is rails. Meaning, once you figure out the query to get the result you want, its a simple matter of using rails to run it. In you case, the mysql query will look something like this: SELECT user\_id, SUM(value) AS total FROM points GROUP BY user\_id ORDER BY total DESC that query will generate something like: user\_id total 1 100 2 50 3 45 where the second column is the total points associated with each user\_id. Now if you want to get the user details not just the id of the user, you will have to do a join. SELECT u.\*, SUM(p.value) AS total FROM users u, points p WHERE u.id=p.user\_id GROUP BY p.user\_id ORDER BY total DESC If I am understanding you correctly, that is the query you want. It will produce something like id name created\_at total 1 john mar-3-06 100 2 sera jun-6-96 50 3 etc. Now that you know what the query looks like, you can make it work in rails in various ways. The easiest one probably is to use find\_by\_sql. User.find\_by\_sql "SELECT u.\*, SUM(p.value) AS total " + "FROM users u, points p " + "WHERE u.id=p.user\_id " + "GROUP BY p.user\_id " + “ORDER BY total DESC” You could always go another route and choose to break down that big query in to parts by utilizing the :join, :order options of the find method. I hope that helped. If you were confused by how to construct the query, I highly recommend reading up on SQL. I guarentee that you will find it useful. ~Rohith ---
**Author:** ![JDJDJD](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e36b37/32.png) [@JDJDJD](https://www.ruby-forum.com/u/JDJDJD)\ **Post date:** [June 23, 2006, 4:13am UTC](https://www.ruby-forum.com/t/best-way-to-generate-sums-of-groups/64184/3 "2006-06-23T04:13:34Z")
> You could always go another route and choose to break down that big > query in > to parts by utilizing the :join, :order options of the find method. That is extremely helpful, thanks! I understand the SQL query, I guess I was trying to avoid using find\_by\_sql because people keep telling me that there are easier and/or better ways to do this in rails. But it looks here like find\_by\_sql is pretty easy too. Could someone show me what this query would look like if done in rails, using the find method and :join and :order? ---
**Author:** ![JDJDJD](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e36b37/32.png) [@JDJDJD](https://www.ruby-forum.com/u/JDJDJD)\ **Post date:** [June 22, 2006, 11:53pm UTC](https://www.ruby-forum.com/t/best-way-to-generate-sums-of-groups/64184/4 "2006-06-22T23:53:54Z")
> User.find\_by\_sql "SELECT u.\*, SUM(p.value) AS total " + > highly recommend reading up on SQL. I guarentee that you will find it useful i agree also see: [http://rubyonrails.org/api/classes/ActiveRecord/Calculations/ClassMethods.html](http://rubyonrails.org/api/classes/ActiveRecord/Calculations/ClassMethods.html) for stuff you can do without needing to dive into SQL…