SQL Joins and Aggregate Functions
Business datasets are often distributed across multiple database tables. Customer information may be stored separately from transaction records, while product details may exist in another table.
SQL joins combine related information, while aggregate functions calculate metrics such as total revenue, average spending, and transaction counts.
SQL joins connect rows from related tables using matching conditions. GROUP BY and aggregate functions summarise observations to support statistical analysis and business reporting.
These operations are important for preparing analytical datasets and creating customer-level or product-level features.
What Are Aggregate Functions in SQL?
Aggregate functions summarise multiple observations into a single value.
Common SQL aggregate functions include:
| Function | Purpose |
|---|---|
COUNT() |
Counts matching values or rows |
SUM() |
Calculates a total |
AVG() |
Calculates an arithmetic average |
MIN() |
Returns the minimum |
MAX() |
Returns the maximum |
Consider this transaction table.
| order_id | customer_id | category | amount |
|---|---|---|---|
| 101 | C001 | Electronics | 12000 |
| 102 | C002 | Clothing | 3500 |
| 103 | C001 | Electronics | 8000 |
| 104 | C003 | Home Decor | 2500 |
| 105 | C002 | Clothing | 6000 |
Calculating Total Revenue
SELECT
SUM(amount) AS total_revenue
FROM orders;Output:
| total_revenue |
|---|
| 32000 |
The query calculates total order value across the sample.
Calculating Average Order Value
SELECT
AVG(amount) AS average_order_value
FROM orders;Output:
| average_order_value |
|---|
| 6400 |
The calculation divides the total amount by five non-null order amounts.
Counting Records
SELECT
COUNT(*) AS total_orders
FROM orders;Output:
| total_orders |
|---|
| 5 |
COUNT(*) counts rows, including those containing null values.
COUNT(column_name) counts non-null values in the specified column.
What Is GROUP BY in SQL?
GROUP BY combines records sharing specified values and allows aggregate calculations for each group.
For example, an e-commerce company may calculate revenue by product category.
SELECT
category,
SUM(amount) AS total_revenue
FROM orders
GROUP BY category;Output:
| category | total_revenue |
|---|---|
| Electronics | 20000 |
| Clothing | 9500 |
| Home Decor | 2500 |
Electronics generates the highest total revenue in this sample.
Applying Multiple Aggregate Functions
SELECT
category,
COUNT(*) AS order_count,
SUM(amount) AS total_revenue,
AVG(amount) AS average_order_value
FROM orders
GROUP BY category;The query calculates transaction count, revenue, and average order value for each category.
These metrics describe different aspects of business activity.
For example, a category may have many transactions but a relatively low average order value.
What Is the HAVING Clause?
HAVING filters groups after aggregation.
Consider identifying categories generating more than ₹10,000 in total revenue.
SELECT
category,
SUM(amount) AS total_revenue
FROM orders
GROUP BY category
HAVING SUM(amount) > 10000;Output:
| category | total_revenue |
|---|---|
| Electronics | 20000 |
WHERE vs HAVING
| Clause | Purpose | Evaluation Stage |
|---|---|---|
WHERE |
Filters individual rows | Before grouping |
HAVING |
Filters aggregated groups | After grouping |
For example:
SELECT
category,
SUM(amount) AS total_revenue
FROM orders
WHERE amount > 3000
GROUP BY category
HAVING SUM(amount) > 10000;The WHERE clause first removes individual orders worth ₹3,000 or less.
The remaining orders are grouped, and HAVING filters the aggregated results.
What Are SQL Joins?
SQL joins combine rows from different tables using specified matching conditions.
Consider two tables.
Customers
| customer_id | customer_name | city |
|---|---|---|
| C001 | Aarav | Delhi |
| C002 | Priya | Mumbai |
| C003 | Rohan | Pune |
| C004 | Meera | Jaipur |
Orders
| order_id | customer_id | amount |
|---|---|---|
| 101 | C001 | 12000 |
| 102 | C002 | 3500 |
| 103 | C001 | 8000 |
| 104 | C003 | 2500 |
The common column is customer_id.
A join can combine customer attributes with order information.
What Is an INNER JOIN?
An INNER JOIN returns rows satisfying the join condition in both tables.
SELECT
c.customer_name,
c.city,
o.order_id,
o.amount
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id;Output:
| customer_name | city | order_id | amount |
|---|---|---|---|
| Aarav | Delhi | 101 | 12000 |
| Priya | Mumbai | 102 | 3500 |
| Aarav | Delhi | 103 | 8000 |
| Rohan | Pune | 104 | 2500 |
Meera does not appear because there is no matching order.
An inner join can return multiple rows per customer when that customer has multiple orders.
What Is a LEFT JOIN?
A LEFT JOIN returns all rows from the left table and matching rows from the right table.
Unmatched right-table columns contain NULL.
SELECT
c.customer_name,
o.order_id,
o.amount
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id;Output:
| customer_name | order_id | amount |
|---|---|---|
| Aarav | 101 | 12000 |
| Aarav | 103 | 8000 |
| Priya | 102 | 3500 |
| Rohan | 104 | 2500 |
| Meera | NULL | NULL |
A left join is useful when analysing all customers, including those without purchases.
Calculating Spending for Every Customer
SELECT
c.customer_id,
c.customer_name,
COALESCE(SUM(o.amount), 0) AS total_spending
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
GROUP BY
c.customer_id,
c.customer_name;COALESCE() replaces a null aggregate result with the first available non-null value.
For customers without orders, total spending becomes zero.
This differs from customers whose spending information is genuinely missing or unknown. Those cases should be handled according to the data model.
What Are RIGHT JOIN and FULL OUTER JOIN?
A RIGHT JOIN retains all rows from the right table.
A FULL OUTER JOIN retains rows from both tables, including unmatched records.
| Join Type | Returned Rows |
|---|---|
| INNER JOIN | Matching rows |
| LEFT JOIN | All left rows and matching right rows |
| RIGHT JOIN | All right rows and matching left rows |
| FULL OUTER JOIN | All rows from both tables |
| CROSS JOIN | Every combination of left and right rows |
PostgreSQL supports these join types.
Database support can differ across SQL systems.
What Is a Many-to-Many Join Problem?
Joins can unexpectedly increase the number of rows when both tables contain repeated matching keys.
For example, a customer may have multiple orders and multiple support tickets.
Joining the orders table directly with the support tickets table using only customer_id can produce combinations of orders and tickets.
This may inflate counts or totals.
A safer approach is to aggregate each dataset to the required observation level before joining.
For customer-level analysis:
Orders → Customer-Level Aggregation → Join With Customer-Level Support Metrics
Understanding the join relationship is important for reliable data science feature engineering.
How Are Joins and Aggregations Used in Data Science?
SQL joins and aggregations support:
- Customer lifetime spending calculations
- Customer order-frequency analysis
- Product-category performance reporting
- Regional revenue analysis
- Churn prediction features
- Transaction-level fraud datasets
For example, a churn prediction dataset may combine subscription information with customer usage summaries and payment-failure counts.
Common SQL Mistakes
- Using an inner join when unmatched records must be retained.
- Ignoring duplicated rows caused by many-to-many relationships.
- Confusing
WHEREandHAVING. - Using
COUNT(column)when all rows should be counted. - Failing to account for null aggregate results.
- Aggregating data at the wrong observation level.
Data Science Tutorial for Beginners
Vaishali Ardhana








