Group and having in Ruby on Rails - Practice Problems & Coding Challenges

Challenge - 5 Problems

📝 Syntax

intermediate

Identify the correct ActiveRecord query using GROUP BY and HAVING

Which of the following Rails ActiveRecord queries correctly groups users by their city and selects only those cities having more than 5 users?

A User.group(:city).having('count > 5').count

B User.group(:city).having(count > 5).count

C User.group(:city).having('count(*) > 5').count

D User.group(:city).where('count(*) > 5').count

💡 Hint

Remember that having expects a SQL condition as a string, and where cannot be used with aggregate functions directly.

Option C correctly uses having('count(*) > 5') to filter groups by count. Option C incorrectly uses where with an aggregate function, which is invalid. Option C uses an undefined variable count without quotes, causing an error. Option C uses count without parentheses in the SQL string, which is invalid SQL syntax.

❓ component_behavior

intermediate

What does this ActiveRecord query return?

Given the query Order.group(:status).having('sum(total) > 1000').sum(:total), what is the output?

Ruby on Rails

Order.group(:status).having('sum(total) > 1000').sum(:total)

A A hash with statuses as keys and their total sums, only for statuses where sum(total) > 1000

B An array of total sums for all statuses regardless of sum(total)

C A single integer representing the sum of all orders with total > 1000

D An error because sum cannot be used with having

💡 Hint

Think about what group, having, and sum do together in ActiveRecord.

The query groups orders by status, filters groups where the sum of total is greater than 1000, then returns a hash mapping each status to its sum total. Option A ignores the having clause. Option A misunderstands the grouping and aggregation. Option A is incorrect because sum can be used with having.

🔧 Debug

advanced

Why does this query raise an error?

Consider this Rails query:

User.group(:role).having('count(id) > ?', 3).count

It raises an error. What is the cause?

Ruby on Rails

User.group(:role).having('count(id) > ?', 3).count

A ActiveRecord does not support parameter binding in having clauses

B The count method cannot be chained after having

C The SQL syntax in having is invalid because count(id) needs aliasing

D The group method must come after having

💡 Hint

Think about how ActiveRecord handles SQL fragments with parameters in having.

ActiveRecord does not support parameter binding (the question mark placeholder) inside having clauses. This causes an error. Option A is false because chaining count is valid. Option A is incorrect because aliasing is not required here. Option A is wrong because group must come before having.

🧠 Conceptual

advanced

Understanding the difference between WHERE and HAVING in ActiveRecord

Which statement best explains the difference between where and having clauses in ActiveRecord queries?

A <code>where</code> and <code>having</code> are interchangeable in ActiveRecord

B <code>where</code> filters groups after aggregation; <code>having</code> filters rows before grouping

C <code>where</code> is used only for joins; <code>having</code> is used only for selects

D <code>where</code> filters rows before grouping; <code>having</code> filters groups after aggregation

💡 Hint

Think about when filtering happens in SQL query execution order.

where filters individual rows before grouping happens. having filters the grouped results after aggregation. They serve different purposes and are not interchangeable.

❓ state_output

expert

What is the count of groups returned by this query?

Given the following ActiveRecord query:

Product.group(:category).having('avg(price) < 50').count.keys.size

What does this expression return?

Ruby on Rails

Product.group(:category).having('avg(price) < 50').count.keys.size

A The total number of products with price less than 50

B The number of categories where the average price is less than 50

C The total number of categories regardless of average price

D Raises an error because keys.size is invalid here

💡 Hint

Consider what count returns when used after group and having.

The count method returns a hash with keys as categories and values as counts. Using keys.size counts how many categories meet the having condition (average price < 50). Option B confuses product count with group count. Option B ignores the having filter. Option B is incorrect because keys.size is valid on the hash.

Practice

1. What does the group method do in Rails ActiveRecord queries?

easy

A. It organizes records by shared column values into groups.

B. It filters records based on a condition.

C. It sorts records in ascending order.

D. It deletes duplicate records from the database.

Solution

1. Step 1: Understand the purpose of group

   The group method collects records that share the same value in a specified column.

2. Step 2: Compare with other options

   Filtering is done by where, sorting by order, and deleting duplicates is not the role of group.

3. Final Answer:

   It organizes records by shared column values into groups. -> Option A

4. Quick Check:

   Group = Organize by shared values [OK]

Hint: Group means collect by same values, not filter or sort [OK]

Common Mistakes:

• Confusing group with where filtering
• Thinking group sorts data
• Assuming group deletes duplicates

2. Which of the following is the correct syntax to group users by their city in Rails?

easy

A. User.group(:city).having(:count)

B. User.group_by(:city).count

C. User.group(:city).where(count)

D. User.group(:city).count

Solution

1. Step 1: Identify correct method name for grouping

   The correct ActiveRecord method is group, not group_by.

2. Step 2: Check chaining methods

   count can be chained after group to count grouped records. where and having require conditions, not just symbols.

3. Final Answer:

   User.group(:city).count -> Option D

4. Quick Check:

   Correct syntax uses group(:column) [OK]

Hint: Use group(:column), not group_by, for ActiveRecord grouping [OK]

Common Mistakes:

• Using Ruby's group_by instead of ActiveRecord group
• Trying to use having without conditions
• Chaining where incorrectly after group

3. Given the query Order.group(:status).having('count(*) > 5').count, what does it return?

medium

A. All statuses with their counts regardless of count value.

B. An error because having cannot be used with count.

C. A hash of statuses with counts only where count is more than 5.

D. A list of orders filtered by status count less than 5.

Solution

1. Step 1: Understand group and having usage

   group(:status) groups orders by status. having('count(*) > 5') filters groups with more than 5 orders.

2. Step 2: Analyze the count method

   count returns a hash with status keys and their counts, but only for groups passing the having condition.

3. Final Answer:

   A hash of statuses with counts only where count is more than 5. -> Option C

4. Quick Check:

   Group + having filters groups by count [OK]

Hint: Having filters grouped results by condition, count returns hash [OK]

Common Mistakes:

• Thinking having filters individual records
• Assuming count returns array, not hash
• Believing having causes syntax error here

4. Identify the error in this query: Product.group(:category).having(count > 10).count

medium

A. category is not a valid column for grouping.

B. The having clause should be a string or SQL fragment, not a variable.

C. count cannot be chained after having.

D. group cannot be used with having in ActiveRecord.

Solution

1. Step 1: Check having syntax

   The having method expects a SQL string or expression, not a Ruby variable like count.

2. Step 2: Validate chaining methods

   Using group with having and count is valid. The error is in how having is written.

3. Final Answer:

   The having clause should be a string or SQL fragment, not a variable. -> Option B

4. Quick Check:

   Having needs SQL string condition [OK]

Hint: Use SQL string inside having, not Ruby variables [OK]

Common Mistakes:

• Passing Ruby variables directly to having
• Thinking group and having can't be combined
• Misunderstanding count chaining

5. You want to count orders grouped by customer country, but only for countries with more than 3 orders total. Which query correctly achieves this?

hard

A. Customer.joins(:orders).group('customers.country').having('count(orders.id) > 3').count

B. Customer.group(:country).having('orders.count > 3').count

C. Customer.joins(:orders).group(:country).where('count(orders.id) > 3').count

D. Customer.group(:country).count.having('count > 3')

Solution

1. Step 1: Join customers with orders

   To count orders per customer country, join customers with orders using joins(:orders).

2. Step 2: Group by customers' country and filter groups

   Group by customers.country and use having('count(orders.id) > 3') to filter countries with more than 3 orders total.

3. Step 3: Count grouped results

   Use count to get the total order count per country meeting the condition.

4. Final Answer:

   Customer.joins(:orders).group('customers.country').having('count(orders.id) > 3').count -> Option A

5. Quick Check:

   Join + group + having with SQL count condition [OK]

Hint: Join related table before group and having with count condition [OK]

Common Mistakes:

• Using where instead of having for group filters
• Not joining orders before grouping
• Passing Ruby method calls inside having