ActiveRecord counting grouped items AND total

rubyonrails-talk
1

This gives me the number of people of each gender born in each year:

Person.group(:gender).group_by_year(:birth_date).count(:id)

But I also want the total number of people born in each year, irrespective of gender. Of course this could be as simple as adding up the counts, but what if I wanted the query to automagically return a “total” for each year? I know it can be done, but my AR Fu is just too weak and it’s proving difficult to extract an answer through the Intertubes…

2

Basically, I want to get rid of this mess, which injects the “total” for each year:

results = Person.group(:gender).group_by_year(:birth_date).count(:id)
results.merge!(results.map{|count| Hash[["total", count[0][1]], count[1]]}
  .inject{|a,b| a.merge(b){|_,x,y| x + y}})

It would be much nicer to do this in the DB.

3

I know it can be done

How? With 2 queries or 1 - if you have a working SQL query it might be easier to convert to AR.

I haven’t been able to get the grouped counts returned in the query as they are already returned as distinct tuples

> Person.group(:gender).group(:birth_date).count(:id)

{
  ["f", Wed, 01 Jan 2025] => 2,
  ["m", Wed, 01 Jan 2025] => 1,
  ["m", Sun, 02 Feb 2025] => 2
}

How would you even expect the result to look like?

4

How would you even expect the result to look like?

MySQL, at least, supports this using GROUP BY ... WITH ROLLUP. An example from https://dev.mysql.com/doc/refman/8.4/en/group-by-modifiers.html:

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
| NULL |   7535 |
+------+--------+

In Ruby, that would look something like this:


{
  ["f", Wed, 01 Jan 2025] => 2,
  ["m", Wed, 01 Jan 2025] => 1,
  [nil, Wed, 01 Jan 2025] => 3,
  ["m", Sun, 02 Feb 2025] => 2
  [nil, Sun, 02 Feb 2025] => 2
  [nil, nil] => 5
}

The final [nil, nil] row is counting the total across all groups (i.e. all genders across all years), which may or may not be what the OP is expecting.

I don’t know how you’d do this in ActiveRecord though.

2 Likes
5

Ah I’m more of a postgres and sqlite guy :wink:

SO without setting up mysql myself I’m not able to try but give this a go:

> Person.group(:gender).count
  Person Count (0.1ms)  SELECT COUNT(*) AS "count_all", "people"."gender" AS "people_gender" FROM "people" GROUP BY "people"."gender"
=> {"f" => 2, "m" => 3}

> Person.group(:gender, :birth_date).count
  Person Count (0.1ms)  SELECT COUNT(*) AS "count_all", "people"."gender" AS "people_gender", "people"."birth_date" AS "people_birth_date" FROM "people" GROUP BY "people"."gender", "people"."birth_date"
=> {["f", Sat, 01 Jan 2000] => 2, ["m", Sat, 01 Jan 2000] => 1, ["m", Sun, 02 Feb 2020] => 2}

Those two work as expected.

But we can also pass a string instead of a symbol to group - we’ll loose some table namespacing but that is not needed for this query anyways (otherwise I’m sure it can be hard coded in the string or maybe with Arel.

Person.group('gender, birth_date with rollup').count
  Person Count (0.4ms)  SELECT COUNT(*) AS "count_all", gender, birth_date with rollup AS "gender_birth_date_with_rollup" FROM "people" GROUP BY gender, birth_date with rollup
(app):8:in '<main>': SQLite3::SQLException: near "rollup": syntax error: (ActiveRecord::StatementInvalid)

So we seem to have the right query, it just doesn’t work in my DB system.

6

Apologies for my absence from the thread - I really appreciate you guys trying to help - but I’m currently dealing with some JS for a tricky release I need to push before I come back to this. ROLLUP seems like an intersting potential solution, and it is available also in PGSQL (which is what I use): PostgreSQL ROLLUP

1 Like