Learn SQL: Day 4 – GROUP BY & Aggregate Functions

Welcome to Day 4.

If JOINs tell you how records are related, then GROUP BY tells you how to summarize data.

This is one of the highest-frequency SQL topics in senior Ruby on Rails interviews because reporting, dashboards, analytics, and business metrics all rely on it.

As a Rails developer, we’ve probably written things like:

Order.count

or

Order.sum(:amount)

or

User.group(:city).count

Today we’ll learn what PostgreSQL is actually doing under the hood.

Today’s Goals

By the end of today, you’ll understand:

• Aggregate Functions
• COUNT
• SUM
• AVG
• MIN
• MAX
• GROUP BY
• HAVING
• GROUP BY with JOINs
• ActiveRecord equivalents
• Common interview questions
• Senior-level insights

Step 1: Create Our Practice Database

We’ll use two tables.

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(100)
);
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    amount NUMERIC(10,2),
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT NOW(),
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
);

Insert Users

INSERT INTO users(name, city)
VALUES
('John', 'New York'),
('Mary', 'Chicago'),
('Bob', 'Chicago'),
('Alice', 'Boston');

Insert Orders

INSERT INTO orders(user_id, amount, status)
VALUES
(1,100,'completed'),
(1,250,'completed'),
(1,75,'pending'),
(2,500,'completed'),
(2,300,'completed'),
(3,200,'pending'),
(4,800,'completed'),
(4,150,'cancelled');

Let’s first see the data.

SELECT * FROM users;

SELECT * FROM orders;

Part 1 – Aggregate Functions

Aggregate functions operate on multiple rows and return a single value.

Examples:

Think of them as summary functions.

COUNT()

How many orders exist?

SELECT COUNT(*)
FROM orders;

Result

8

COUNT(column)

SELECT COUNT(status)
FROM orders;

Counts only non-NULL values.

If one status were NULL:

COUNT(*)      = 8
COUNT(status) = 7

This difference is asked surprisingly often in interviews.

Rails Equivalent

Order.count

COUNT(DISTINCT)

How many cities do users belong to?

SELECT COUNT(DISTINCT city)
FROM users;

Result

3

Cities:

• Chicago
• Boston
• New York

Rails:

User.distinct.count(:city)

SUM()

Total revenue:

SELECT SUM(amount)
FROM orders;

Calculation:

100
+250
+75
+500
+300
+200
+800
+150
-------
2375

Rails:

Order.sum(:amount)

AVG()

Average order amount.

SELECT AVG(amount)
FROM orders;

Rails

Order.average(:amount)

MIN()

SELECT MIN(amount)
FROM orders;

Result

75

MAX()

SELECT MAX(amount)
FROM orders;

Result

800

Rails

Order.maximum(:amount)

Part 2 – GROUP BY

This is today’s biggest topic.

Without GROUP BY:

SELECT SUM(amount)
FROM orders;

One answer.

2375

Suppose the business asks:

What’s the total revenue for each customer?

Now we need GROUP BY.

GROUP BY user_id

SELECT
    user_id,
    SUM(amount)
FROM orders
GROUP BY user_id;

Result

Notice:

PostgreSQL divides the rows into groups first.

User 1
100
250
75
↓
425

Then repeats for every user.

Rails Equivalent

Order.group(:user_id).sum(:amount)

Returns:

{
  1 => 425,
  2 => 800,
  3 => 200,
  4 => 950
}

Visualizing GROUP BY

Imagine sorting all orders into buckets.

Orders
↓
User 1 Bucket
100
250
75
↓
425

User 2 Bucket
500
300
↓
800

This is essentially what GROUP BY does conceptually.

GROUP BY City

SELECT
    city,
    COUNT(*)
FROM users
GROUP BY city;

Result

Rails

User.group(:city).count

Common Interview Question

Why doesn’t this work?

SELECT
    user_id,
    amount
FROM orders
GROUP BY user_id;

PostgreSQL responds:

column “amount” must appear in the GROUP BY clause or be used in an aggregate function

Why?

Because each group contains multiple amount values. PostgreSQL doesn’t know which one you want.

For user_id = 1, there are three amounts:

• 100
• 250
• 75

Which one should it return?

It can’t guess.

Rule to Remember

Every selected column must be:

• in GROUP BY, or
• inside an aggregate function.

Correct:

SELECT
    user_id,
    SUM(amount)
FROM orders
GROUP BY user_id;

GROUP BY Multiple Columns

SELECT
    user_id,
    status,
    COUNT(*)
FROM orders
GROUP BY user_id, status;

Result

Now each unique (user_id, status) combination becomes its own group.

GROUP BY with JOIN

Very common interview question.

Show each user’s total spending.

SELECT
    u.name,
    SUM(o.amount) AS total_spent
FROM users u
JOIN orders o
ON u.id = o.user_id
GROUP BY u.name;

Result

Rails

User
  .joins(:orders)
  .group(:name)
  .sum("orders.amount")

What if a User Has No Orders?

Let’s add one.

INSERT INTO users(name, city)
VALUES ('David', 'Mumbai');

Now run:

SELECT
    u.name,
    SUM(o.amount)
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id
GROUP BY u.name;

Result

Notice:

LEFT JOIN keeps David, even though he has no orders.

COALESCE()

Business users usually don’t want NULL.

They want:

0

Use:

SELECT
    u.name,
    COALESCE(SUM(o.amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id
GROUP BY u.name;

Result

We’ll study COALESCE in more detail later, but it’s useful to know this pattern now.

HAVING

Many developers confuse WHERE and HAVING.

This is a favorite interview topic.

Suppose we want:

Show only customers who spent more than 500.

Wrong:

SELECT
    user_id,
    SUM(amount)
FROM orders
WHERE SUM(amount) > 500
GROUP BY user_id;

This fails because WHERE filters individual rows before grouping. At that stage, SUM(amount) hasn’t been calculated yet.

Correct:

SELECT
    user_id,
    SUM(amount)
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 500;

Result

WHERE vs HAVING

Think of the execution order:

FROM
↓
WHERE
↓
GROUP BY
↓
Aggregate Functions
↓
HAVING
↓
SELECT
↓
ORDER BY

• WHERE filters rows before grouping.
• HAVING filters groups after aggregation.

Rails Equivalent

Order
  .group(:user_id)
  .having("SUM(amount) > ?", 500)
  .sum(:amount)

Real Interview Questions

Top Spending Customer

SELECT
    user_id,
    SUM(amount) AS total
FROM orders
GROUP BY user_id
ORDER BY total DESC
LIMIT 1;

Number of Orders Per Status

SELECT
    status,
    COUNT(*)
FROM orders
GROUP BY status;

Average Order Value Per User

SELECT
    user_id,
    AVG(amount)
FROM orders
GROUP BY user_id;

Highest Single Order Per User

SELECT
    user_id,
    MAX(amount)
FROM orders
GROUP BY user_id;

Common Mistakes

Mistake 1

Selecting non-grouped columns.

SELECT user_id, amount
FROM orders
GROUP BY user_id;

Mistake 2

Using WHERE instead of HAVING for aggregate conditions.

Mistake 3

Using an INNER JOIN when the business wants users with zero orders.

Use a LEFT JOIN plus COALESCE.

Senior-Level Insights

1. GROUP BY changes the shape of your data. You no longer have one row per order—you have one row per group.
2. Aggregate functions ignore NULL values (except COUNT(*), which counts rows regardless).
3. Always think about the business question first. Ask yourself:
   • “What am I grouping by?”
   • “What summary do I want for each group?”
4. JOIN + GROUP BY is the foundation of almost every reporting query you’ll write.

Practical Exercises

1. Count the total number of users.
2. Calculate the total revenue from completed orders only.
3. Find the average order amount for each user.
4. Count how many orders each status has.
5. Find users who have placed more than two orders.
6. Show each city and the total number of users in that city.
7. Add another user with no orders and show them with a total spend of 0.
8. Find the highest order amount for every user.

Homework

Create two new tables:

departments

employees

Insert data for at least:

• 3 departments
• 10 employees

Then write SQL to answer:

1. Number of employees per department.
2. Average salary per department.
3. Highest salary per department.
4. Departments with more than 3 employees.
5. Departments with no employees (hint: LEFT JOIN + GROUP BY).

Also write the Rails/ActiveRecord equivalent for each query.

Interview Challenge (Don’t Run It Yet)

Without executing the query, tell me what you think this returns:

SELECT
    status,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS average_amount
FROM orders
WHERE amount >= 100
GROUP BY status
HAVING COUNT(*) >= 2
ORDER BY total_amount DESC;

In senior interviews, you’re often asked to reason about a query rather than just write it. Being able to mentally execute SQL like this is a valuable skill.

Day 5 Preview

Tomorrow we’ll move to another core interview topic:

Filtering, Subqueries & EXISTS

We’ll cover:

• Scalar subqueries
• Correlated subqueries
• IN vs EXISTS
• NOT EXISTS
• ANY and ALL
• Common Rails equivalents
• Performance considerations
• Real interview questions and optimization tips

These concepts are widely used in production applications and are common in senior backend interviews.

Happy Learning! 

Author: Abhilash

Hi, I’m Abhilash! A seasoned web developer with 15 years of experience specializing in Ruby and Ruby on Rails. Since 2010, I’ve built scalable, robust web applications and worked with frameworks like Angular, Sinatra, Laravel, Node.js, Vue and React. Passionate about clean, maintainable code and continuous learning, I share insights, tutorials, and experiences here. Let’s explore the ever-evolving world of web development together! View all posts by Abhilash