Intermediate SQL: Data Aggregation

Stephen Ayeni

17 min read

·

Mar 11, 2026

--

1

Listen

Share

Source: Intermediate SQL by Data Camp

Press enter or click to view image in full size

In this Section: Intermediate SQL, we will be discussing the following topic areas:

1. Preview Data to understand its structure and content
2. Think through the steps needed to solve a data problem before you solve it
3. Apply Core SQL operations (SUMMARY, FILTER, SORT and TRANSFORM etc.)
4. Write concise, reliable SQL to execute operations
5. Use AI tools like ChatGPT to accelerate learning and troubleshoot issues

Data Aggregation: This is one of the most powerful operations. It lets you summarise data into meaningful insights. Instead of reading 1,000s of rows, you can answer questions like — “How many orders did we fulfil last month?” or “How many cities did we ship to last month?”

Case Study: You are a Business Intelligence Engineer at an eCommerce Company and your company captures data on customers, products and operations with the expectations of using the data to improve customer experience, sales and optimise operations.

Question: What is the total revenue from our orders? How can we calculate this with SQL?

We will first view the data to see what we are working with — remember, we need to use the LIMIT function so that we do not expend a-lot of energy, time and money to view the data

Data set is called “orders”

SELECT *
FROM orders
LIMIT 10;

We view the first 10 rows of data with this code. We are doing this to get an overview of the data set and its structure without wasting time, energy or money.

Now from our SQL query, we can see the the data set includes various columns (or variables) including order_id , user_id and amount . Since we are focused on answering the question on the total revenue, we will just isolate for these variables. Let’s use SQL to view a table with just these variables

SELECT order_id,
        user_id,
        amount
FROM  orders
LIMIT 10;

Note: Specifying the column you want to analyse improves the performance and add clarity. This is because it takes less database processing power and fewer columns to analyse — simplifying the process

Now, how to do we answer the initial question (What is the total revenue?). We know we need the total sum of the value under the amount column. We will use the SUM function for this. This is similar to how to operates in Excel or Google Sheets. Let’s apply the SUM function to the amount section.

SELECT SUM(amount)
FROM orders;

This is how it works:

SUM() functions takes on the column name as input and all the numerical values in that column. In this case, SUM(amount) it adds all the numerical values in the amount column.

Now, let’s label the output of the SQL entry.

Before we do, let’s dive deeper on the concept of Functions as they are a critical concept. Functions have 3 key concepts:

1. Name: Identifies the operations like SUM
2. Inputs: The data they operate on, placed inside a ( ) . For example, SUM(amount) — in this case, amount is the input
3. Outputs: The result after performing the operations SUM(amount) returns the total of all the numerical values in the amount variable

( ) is called parenthesis in programming not brackets.

Functions names are written with parenthesis indoctrination like SUM( ) to separate them from tables and columns like orders or amount

Now, let’s try renaming the total revenue

SELECT SUM(amount) AS total_revenue
FROM orders;

Remember renaming it does not actually change the name in the database. Just in the results of the SQL Query

Principles for naming columns:

1. Valid, 2. Descriptive, 3. Readable

Summary Operations

This transforms a column of values into a single representative value — like sum, average, minimum or maximum and count. Let’s say, we want to know the Smallest Order Amount, Largest Amount and Average Order Amount for the eCommerce company as a Business Intelligence Officer , we will use the following code:

1. Smallest: Apply the MIN( )operators on the amount column
2. Largest: Apply the MAX( ) operators on the amount column
3. Average: Apply the AVG( ) which is the mean operators on the amount column

Let run some tests

-- Minimum Order Amount
SELECT MIN(amount)
FROM orders;

-- Maximum Order Amount
SELECT MAX(amount)
FROM orders;

-- Average Order Amount
SELECT AVG(amount)
FROM orders;

Important Concept: Thinking through a Query before Writing Code

This is one of the most effective habits of a successful Data Analyst (or Business Engineer). When designing a data aggregation operation: Ask yourself two questions:

1. Which column do we wish to summarise?
2. Which summary operations do we wish to apply?

This framework helps you understand the objective, the input and the domain. This is what separates Strong Analyst (or Engineers) from people who just write code.

In interviews, showing you can think systematically before coding is exactly what evaluators look for

Let’s expand on this concept:

1. Objective: Understanding what the question you are trying to answer. This helps a-lot and shapes the whole framework and your code
2. Input: Knowing what’s in your table — which columns exist, what they record and what it means.
3. Domain: Understand the relevant context, you know what each column means in its context (which could be business — if you are an eCommerce company — order amounts means the price paid, if you are a government office — FDI means the ability to attract foreign investors etc.)

Domain knowledge helps you choose the right column and right operations to answer the question in focus

For Example: Let’s say you are an Sport Analyst and a General Manager asks you — which university recruits the best players (academically and athletically)

1. Objective: Which university/college recruits the most athletically and academically talented student-athletes?
2. Input: We have to analyse the datasets we have to identify relevant columns like: Athletically: Which school recruited the most 4 or 5 star recruits over the past few years? Academically: Which college/university recruits the most high school students who finished in the top 10% or 25% of their high school graduation class?
3. Domain: Understand that being 4 or 5 star high school athlete means that you are one of the best in the country at your football position. Understand that finishing in the top 10% or 25% of your high school graduation class means you are one of the best students in your high school.

Please note, we will answer this question in later posts.

COUNT( ) Operators:

This is how it works — COUNT( ) counts all the values in the column that is inserted into the parenthesis.
For example COUNT(order_id) counts all the values in the order_id column. It takes the column name as input and returns a single count.

This is one of the most frequently used functions in SQL. You will constantly use it for Business Metrics and in interviews.

Case Study: How many unique users have placed an order?

1. Objective: Count how many unique users have placed an order
2. Input of Focus: user_id
3. Operators of Focus: COUNT() and DISTINCT()

How do we then use these operators on the input of focus?

To Count Unique entities in a column:

COUNT(DISTINCT user_id)

1. The DISTINCT() operator eliminates duplicates
2. The COUNT() operator then counts only the unique values remaining

This is how we count unique values in a column. We will use this a-lot in Business Analytics as well.

Example: Total numbers of trips on Uber

1. Objective: We want to get the total number of trips in Uber
2. Input of Focus: trip_id
3. Operation of Focus: COUNT()

SELECT COUNT(trip_id) AS total_trips
FROM uber;

Example: The total number of unique riders who took trips

1. Objective: The total number of unique riders who took a trip with Uber
2. Input of Focus: rider_id
3. Operation of Focus: COUNT(DISTINCT)

SELECT COUNT(DISTINCT rider_id)
FROM uber;

Now, what makes SQL so powerful?

In practise, you rarely need one metrics. Understandably, a situation often involves you analysing multiple metrics to get a complete picture. Running a query with all metrics is more efficient than multiple queries asking for each metric.
The results with be neatly arranged in one output table with one row and one column per summary value

How does this work?

1. List multiple operation functions under the SELECT clause, separate them with commas
2. Each function is aliased with AS for clarity
3. You get a query instead of multiple queries which is so much more efficient

Let’s show you an example

1. Total number of trips: trip_id + COUNT() = COUNT(trip_id) AS total_trips
2. Total number of unique riders: riders_id + COUNT(DISTINCT) = COUNT(DISTINCT riders_id) AS total_unique_riders
3. Total number of unique drivers: driver_id + COUNT(DISTINCT) = COUNT(DISTINCT driver_id) AS total_unique_drivers
4. Total fare: fare + SUM() = SUM(fare) AS total_fare
5. Average fare: fare + AVG() = AVG(fare) as average_fare

You can see that this will all be single queries but the power of SQL is that we can just have this

SELECT COUNT(trip_id) AS total_trips,
       COUNT(DISTINCT riders_id) AS total_unique_riders,
       COUNT(DISTINCT driver_id) AS total_unique_drivers,
       SUM(fare) AS total_fare,
       AVG(fare) as average_fare
FROM uber;

Different database exists — some of the most popular databases are: PostgreSQL, MySQL, SQLServer and BigQuery and each database has its own flavour and its syntax.

However, they still have the same fundamental concepts and most common commands.

Note: Speak the language (or database in this case) of the company you want to work at. The earlier, the better

We will be running experiments with all of them here but we will start with Google BigQuery in this course. Some of the syntax you learn here is the same as the other syntax for other database systems so it is transferable.

BigQuery is Google’s server-less data warehouse, built to analyse massive amount of data in seconds. It also has other strong benefits.

1. Industry Standard: Widely accepted across start-ups and enterprise, making it a valuable skill in today’s job market
2. Free to Practise: Google offers a generous free-tier, so you can practise as much as you need without spending anything
3. Transferability: Your skills with Google BigQuery transfers seamless to PostgreSQL, MySQL, SQL Server etc.

Organisation of Databases:

Databases are organised in a hierarchal manner.
Top-level is Database:

1. They contain multiple datasets.
2. Datasets contain multiple data-tables
3. Each table contains rows and columns

BigQuery uses this terminology:

Project -> Dataset -> Tables

In Big Query, tables are named and use a qualified name format:

Qualified Name Format: project.dataset.table

Example: league.team.player -> this will refer to players table in team dataset in league project

We have already learnt about summary values like -> SUM , AVERAGE , MAX , MIN etc. This values can describe an entire dataset. They give us a high-level overview but where it is really powerful is when you want to compare performance across different systems and which customer segment drive orders, revenue. This is what grouped aggregation does.

Multiple Grouping Columns:

What happens when we want to understand how customers behaviour varies across regions or customer segments or analysing UK economic strategies broken down by sectors & time period.

Multi-column groups helps here — they help answer these sort of questions. it help you identify patterns that looking at one dimensions would not reveal

Case Study: eCommerce Company:
We are still analysing order data. Now, let’s imagine the business operates in two: Canada & UK

Job Task:
What are the order counts, user count, revenue, average order values for each categories in each location?

We are going to following a process for analysing tasks like these

1. Step 1: What would the output look like -> Before, we write any code, let’s think about what the output would look like?
   Remember for our previous section: the output look like this

Press enter or click to view image in full size

So what will happen here: Each unique combination would have its own row — so it will look like: Electronics-Canada is one row, Electronics-UK is the next row, Fashion-Canada is the following row and Fashion-UK is the next row.

So just a Quick-Recap: This all started with the question — what if we can compare performance across different segments? Like understanding which customers segment drive the most revenue or which product categories need attention?

This is what grouped aggregations does — it calculates the same summary statistics but groups them by categories. We do this to spot patterns, compare segments and make targeted decisions.

This is constantly used when building reports or analysing behaviour across different groups.

For example: You are a Data Analyst at an eCommerce company (or Business Intelligence Engineer) and it operate in 3 major categories: Electronics, Fashion and Grocery

Question: What are the order counts, user count, revenue and average order value for each category

And now, we are discussing what the process would look like for solving this problem

1. We start off with what we envision the output to look like and

Back to it, The image below is called a Summary Table

Press enter or click to view image in full size

This is the standard way of showing Grouped Aggregation Results. This makes it easy to compare metrics across different segments.

In our earlier note on aggregation— we used a 2 question framework for aggregation:

1. Which columns need summarising?
2. Which summary operation do we apply?

Grouped Aggregation addresses one new question at the beginning — by which columns do we group?

This determines how we split the data into segments before calculating our metrics

Let’s apply this framework to this scenarios

1. By which columns do we group with: Since we want metrics for every category — we will group by the category column
2. Which columns need summarising:
   order_id → Apply COUNT()
user_id -> Apply COUNT(DISTINCT)
category -> Apply grouping by category
   amount -> SUM(amount)
amount -> AVERAGE(amount)

Important: Always check what unique values exist in your column (that you are grouping) before you group it.
Why?
1. Understand your data: It helps you understand the data you are working with — see what segment/categories actually exist
2. Catching issues early: Spot unexpected categories, values, typos or data quality issues
Example: if your categories is colour but you see red , blue fish . You can catch this and investigate this ASAP.
So how do we do this?
We run this code:

SELECT DISTINCT categories
FROM orders;

Now, let’s build out our one grouped Aggregations step by step:

-- Let start by adding COUNT in the revelant column

SELECT COUNT(order_id) AS order_count,
       COUNT(DISTINCT user_id) AS user_count,
       SUM(amount) AS revenue,
       AVG(amount) AS avg_amount
FROM orders;

We will now add two steps to the code above:

1. We will add category in the SELECT clause (since this is the column we are grouping by
2. Add GROUP BY category after the FROM clause

SELECT category,
       COUNT(order_id) AS order_count,
       COUNT(DISTINCT user_id) AS user_count,
       SUM(amount) AS revenue,
       AVG(amount) AS avg_amount
FROM orders
GROUP BY category;

Two Principle: GROUP BY

What goes in SELECT when you have a GROUP BY clause, your SELECT should include:

1. The grouping column (in this case category )
2. Aggregate function applied to the other columns ( COUNT , SUM , AVG etc.)

It is also important to use aliases with AS to give clear, meaningful names.

How Group Aggregations Works?

1. Step 1: Split
   The input data is broken into groups based on unique values. The number of groups = the number of unique values. So in our eCommerce example — when we grouped by category -> The order table was split into separate groups, one for electronics , one for fashion , one for grocery , and one for Home & Garden
2. Step 2: Apply
   Once the data is split, summary operations are applied to each group separately. For our eCommerce example, we did COUNT , SUM and AVG
3. Step 3: Combine
   The results for each group are assembled into a single summary table. Each row represents one group from the splitting steps. In this step (Combine step), each row represents each unique value in the Split step and each column represent the summary operation in the final summary table you see in the query results.

Please note: Not every column makes a good grouping column

Good grouping columns are columns with a few distinctive values like:

1. Categories
2. Location
3. Time-Period
4. Product Types

These naturally divide data into meaningful segments you can compare

Key: Numerical columns with many unique values typically make poor grouping columns. Stick to qualitative columns with few distinct values

Now, on top of this, we can apply the SORT BY function to get a better understanding of our group table

SELECT category,
       COUNT(order_id) AS order_count,
       COUNT(DISTINCT user_id) AS user_count,
       SUM(amount) AS revenue,
       AVG(amount) AS avg_amount
FROM order
GROUP BY category
ORDER BY revenue;

Remember this will automatically sort it in an ascending order

You can also change it to sort it in a descending order like

SELECT category,
       COUNT(order_id) AS order_count,
       COUNT(DISTINCT user_id) AS user_count,
       SUM(amount) AS revenue,
       AVG(amount) AS avg_amount
FROM order
GROUP BY category
ORDER BY revenue DESC;

Recap:

The ORDER BY clause arranges rows in a table based on values in a column. It returns as the same data just re-ordered. You can apply it to any table, whether raw data or summary table

Two reasons to sort:

1. Answer Business Questions: Many questions can relate to finding the highest or the lowest like — highest revenue category or least active segment
2. Visual Clarity: Sorting makes pattern much easier to spot, especially in longer datasets.

SQL requires clauses to be written in a specific order. When writing queries with grouping or sorting, the clause must follow this sequence:

SELECT -> FROM -> GROUP BY -> ORDER BY

This order was established by SQL creators as a language convention for readability. It allows queries to read naturally by starting with:

1. What you want
2. Before choosing where it comes from
3. And what order to arrange it by

When comparing variables for two separate groups to start, we run this code:

SELECT *
FROM orders -- this is the name of the dataset
LIMIT 100;

Remember our business question: What are the order counts, user counts, revenue and average order value for each category in each location

So the first thing we do is ask:

1. Which column(s) do we need to break down in our summary?
   Answer: We need to break it down by location and category . This will give us metrics for every combination: Electronics in Canada, Electronics in UK, Fashion in Canada, Fashion in UK etc.

The key insight: we can break our summary by as many columns as we want

In this business case, we need both dimensions to answer the business question. Now we know the columns we are grouping by, let do the next step in the framework. — we will write down the operations and the variables we are working with and put them together

1. Order Count:
   variable: order_id
   operation: COUNT()
   code: COUNT(order_id)
2. User Count:
   variable: user_id
   operation: COUNT(DISTINCT) — we use this because a user might order multiple times and it will be recorded as multiple entry when there is really just one user
   code: COUNT(DISTINCT user_id)
3. Revenue:
   variable: amount
   operation: SUM()
   code: SUM(amount)
4. Average Order Value:
   variable: amount
   operation: AVG()
   code: AVG(amount)

Now, let makes this a reality.
Remember our best practise, checking what unique combination of our two groups of interest location and category first. This will help us predict how many rows or summary we will have and avoid surprises.

This is the code:

SELECT DISTINCT location,
                 category
FROM order;

First we can see that SELECT DISTINCT works with multiple columns as well. It returns the one row for each unique combination of location and category value. so it returned: UK — Electronics, UK-Fashion, UK-Grocery, UK-Home&Garden, Canada-Electronics, Canada-Fashion, Canada-Grocery.

After this, we can now group and calculate our metrics. Let’s do it

SELECT location,
       category,
       COUNT(order_id) AS order_count,
       COUNT(DISTINCT user_id) AS user_count,
       SUM(amount) AS revenue,
       AVG(amount) AS average_amount
FROM orders
GROUP BY category,
         location;

Let’s break this down:

1. Which variables are we grouping this by: We select which variables we are grouping by in this case — location and category
2. What metrics are we interested in studying for these variables: COUNT(order_id) AS order_count this is the number of orders we have in the dataset. COUNT(DISTINCT user_id) AS user_count this counts all the distinct users for each group, SUM(amount) AS revenuethis calculates the revenue. AVG(amount) AS avg_revenue this calculates the average revenue. All of these are calculated for each unique combination of location and category
3. Which dataset are we pulling these data from: We are pulling them orders dataset.
4. How do we want to present the data:
GROUP BY location,
category;

Quick Summary: to get a summary table for any combination of 2 variables, we have to do the following:

1. Add the grouping columns with SELECT function — separated by commas.
2. Add the metrics you want to study for each group ( SUM() , COUNT() , AVG()
3. For SQL to present the intended summary table, use GROUP BY for the the grouping columns and separate by commas.
4. SQL will handle the rest :)

Note: Instead of using column names, we can use column position in the GROUP BY function. The number refer to the position of the column in the SELECT clause so 1 means 1st column location , 2 mean 2nd column category so while this works, it is heavily recommended that you use the column names for 2 reasons:

1. Readability: It is immediately clear to other people (and yourself) what you are grouping by
2. Safety: If you change the SELECT clause in the future, it can break the position or group by the wrong columns

Rule 1: Every non-aggregated column in the SELECT must appear in GROUP BY . If you select both category and location — it must be grouped by both This is a common source of errors. Please keep this in mind

Rule 2 : Always include group size in your summary. A small sample size can be misleading and there is might not be enough information to make definitive decision on a population

How Large should a group by to trust its metrics?

Group Size requirement varies based on context but here are 3 questions, you can ask that will help:

1. How varied is this group:
   Group with a-lot of diversity read more data points size than a group where everyone is similar. Example: Analysing all families in Manchester requires more data points than young, Nigeria Families in Central Manchester.
2. How much does this decision matter:
   A decision affecting million of dollars or many people need more reliable data than a low stake choice. For a government research on economy, deciding national policy needs strong evidence than exploring initial trends
3. What data can you realistically get:
   Sometimes the ideal group size is not possibly. We will work with what we have while making note of the limitation. A small region might only have 10 African families, we can’t magically produce more so we use it BUT we note the limitations

General guideline: Aim for a size of 30 or more samples points for reliable summaries. This is statistical reference but please use the guidelines noted above

Recap:

Multi-column sorting: This is sorting using multiple columns instead of just one column. It gives you finer control over row ordering by sorting on multiple columns in a sequence.

This is valuable for the following reasons:

1. Identifying top or bottom values (like highest revenue categories by locations)
2. Making data easier to visually inspect (grouping related items together)
3. Organising results for presentation or further analysis.

One Last concept to discuss — the order of sorting columns significantly affect results. Reversing column order yields an entirely different result.

This highlights the ORDER BY location, revenue DESC;

Let’s show you what this looks like

-- OLD CODE
SELECT location,
       category,
       COUNT(order_id) AS order_count,
       COUNT(DISTINCT user_id) AS user-count,
       SUM(amount) AS revenue,
       AVG(amount) AS avg_amount
FROM orders,
GROUP BY location, category,
ORDER BY location, revenue DESC;

and this is the new code:

-- NEW CODE
SELECT location,
       category,
       COUNT(order) AS order_count,
       COUNT(DISTINCT user_id) AS user_count,
       SUM(amount) AS revenue,
       AVG(amount) AS avg_amount
FROM orders,
GROUP BY location, category
ORDER BY revenue, location DESC;

In the new code, we can see that it is just sort by revenue — why is this? since each location-category combination has a unique revenue value — so the secondary sort by location has no visible effect

In the initial code, it just order by location (All Canada categories, ALL UK categories) and then it re-arranges it in order of revenue with each location

The key insight here is the primary (1st) ORDER BY column controls the main ordering, the secondary (2nd) column only matters when there are ties (or connection) with each row

Thank you — God!
This took so long