Group and having in Ruby on Rails - Mini Project: Build & Apply

Using Group and Having in Rails ActiveRecord Queries

📖 Scenario: You are building a simple Rails app to analyze sales data. You have a Sale model with product_name and amount attributes. You want to find products that have total sales above a certain amount.

🎯 Goal: Build an ActiveRecord query that groups sales by product_name, sums the amount for each product, and filters to only include products with total sales greater than 100.

📋 What You'll Learn

Create a list of sales with exact product names and amounts

Set a threshold variable for minimum total sales

Write an ActiveRecord query using group and having to filter products

Return the filtered grouped sales data

💡 Why This Matters

🌍 Real World

Grouping and filtering data by aggregate values is common in sales reports, analytics dashboards, and business intelligence tools.

💼 Career

Understanding how to use ActiveRecord's group and having methods is essential for Rails developers working with databases and building data-driven features.

1

Create initial sales data

Create a variable called sales that is an array of hashes with these exact entries: { product_name: 'Book', amount: 50 }, { product_name: 'Pen', amount: 30 }, { product_name: 'Book', amount: 60 }, { product_name: 'Notebook', amount: 120 }.

Ruby on Rails

# Create the sales array with product_name and amount hashes
# Your code here

Hint

Use an array of hashes with keys :product_name and :amount.

2

Set the sales threshold

Create a variable called min_total_sales and set it to 100.

Ruby on Rails

sales = [
  { product_name: 'Book', amount: 50 },
  { product_name: 'Pen', amount: 30 },
  { product_name: 'Book', amount: 60 },
  { product_name: 'Notebook', amount: 120 }
]
# Set the minimum total sales threshold
# Your code here

Hint

Just assign the number 100 to min_total_sales.

3

Write the ActiveRecord group and having query

Write an ActiveRecord query called result on the Sale model that groups by product_name, selects product_name and sum of amount as total_amount, and uses having to filter where total_amount is greater than min_total_sales.

Ruby on Rails

sales = [
  { product_name: 'Book', amount: 50 },
  { product_name: 'Pen', amount: 30 },
  { product_name: 'Book', amount: 60 },
  { product_name: 'Notebook', amount: 120 }
]
min_total_sales = 100
# Write the ActiveRecord query using group and having
# Your code here

Hint

Use group(:product_name), select(:product_name, 'SUM(amount) AS total_amount'), and having('SUM(amount) > ?', min_total_sales).

4

Return the filtered grouped sales data

Add a line to return the result variable.

Ruby on Rails

sales = [
  { product_name: 'Book', amount: 50 },
  { product_name: 'Pen', amount: 30 },
  { product_name: 'Book', amount: 60 },
  { product_name: 'Notebook', amount: 120 }
]
min_total_sales = 100
result = Sale.group(:product_name)
             .select(:product_name, 'SUM(amount) AS total_amount')
             .having('SUM(amount) > ?', min_total_sales)
# Return the result variable
# Your code here

Hint

Just add result on a line by itself to return it.

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