Menu

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

  1. Using an inner join when unmatched records must be retained.
  2. Ignoring duplicated rows caused by many-to-many relationships.
  3. Confusing WHERE and HAVING.
  4. Using COUNT(column) when all rows should be counted.
  5. Failing to account for null aggregate results.
  6. Aggregating data at the wrong observation level.

Data Science Tutorial for Beginners

VA

Vaishali Ardhana



Learn via Video Course

undefined Logo

0 hrs



Get in Touch For Details