Rails Activerecord Perform Group, Sum and Count in One Query

Get sum of a group by count

You won't be able to do this in a single SQL query unless you craft some fancy SQL yourself. If performance is not a problem, I would aggregate this on the controller:

counts = User.group(:city).count
total = counts.values.sum

Rails ActiveRecord Query: Sum total article view count per category

If I understand you correctly and view count is impressions_count, here is query that you can use:

Article.joins(:categories).group('categories.name').sum(:impressions_count)

Select, group and sum results from database

Fixed it by using :select when getting the query, inputing selects manually

@hours = Hour.all(:conditions => "user_id = "+ @user.id.to_s,
                  :select => "created_at, SUM(time) time",
                  :group => "strftime('%m', created_at)",
                  :order => 'created_at DESC')

GROUP BY and COUNT using ActiveRecord

Distinct and Group By are going to give you different results. To get the results you expect you'll want to use

Person.group(:name).count
(1.2ms)  SELECT COUNT(*) AS count_all, name AS name FROM "people" GROUP BY "people"."name"
=> {"Dan"=>3, "Dave"=>2, "Vic"=>1} 

Seen above, group will return things as a hash. While distinct just returns the number of people in total, seen below.

Person.distinct(:name).count
(0.4ms)  SELECT DISTINCT COUNT(DISTINCT "people"."id") FROM "people"
=> 6 

Rails get sum of field grouped by using includes

Try to the following:

Referral
  .joins(:status, application: :detail)
  .group('statuses.assignment_status')
  .select('statuses.assignment_status, sum(details.purchase_price), count(*)')

Sum up in group by query on single table

So, I'm assuming you're using MySQL and that you have a seperate states table and the states in the other columns are referenced by ID, like so:

| ID | Name   |
| 1  | new    |
| 2  | closed |
| 3  | open   |

So by executing this SQL statement in MySQL

SELECT states.name AS state, DATE_FORMAT(tasks.created_at, '%Y-%m') AS month,
       COUNT(tasks.id) AS count, SUM(tasks.hours) AS hours
  FROM states JOIN tasks ON states.id = tasks.state_id
GROUP BY state, month
ORDER BY month

you should get a result like this

| state  | month   | count | hours |
| open   | 2012-03 | 22    | 38.5  |
| open   | 2012-04 | 17    | 40.0  |
| closed | 2012-03 | 45    | 0.5   |

So, you just need to bring this query to an ActiveRelation query, or you could use Task.find_by_sql.
It's not the exact same way you described how you want your result to look, but with this set you can alter it using plain ruby, after executing...

Group by and Sum in Rails 3

Hey you can used having clause as

Demo.group('id').having("sum(percentage) < 100")

---

Related Topics