> ## Content Index
> Fetch the complete content index at: https://ksnk.media/llms.txt
> Use this file to discover other available public pages before exploring further.

# Use subqueries to aggregate data {SQL}
- URL: https://ksnk.media/use-subqueries-to-aggregate-data-sql/
- Published: 2024-05-20T11:49:58.000Z
- Updated: 2026-05-15T13:46:52.000Z
- Description: Learn how SQL subqueries help aggregate data, compare results, and build cleaner analysis queries without manual spreadsheet work.
- Author: Aleksandr Kosenko
- Tags: Blog

[WarehouseWarehouse.csv484 Bytesdownload-circle](https://ksnk.media/content/files/2024/05/Warehouse.csv "Download")

[OrdersOrders.csv352 KBdownload-circle](https://ksnk.media/content/files/2024/05/Orders.csv "Download")

## HAVING function

Allows you to add a filter to your query instead of the underlying table that can only be used with aggregate functions

## CASE function

Returns records with your conditions by allowing you to include if/then statements in your query

## Aliasing

When you temporarily name a table or column in your query to make it easier to read and write

## Objective

The objective of this query is to aggregate the data into a table containing each warehouse's ID, state and alias, and number of orders; as well as the grand total of orders for all warehouses combined; and finally a column that classifies each warehouse by the percentage of grand total orders that it fulfilled: 0–20%, 21-60%, or > 60%.

**Note:** This activity breaks out the steps into manageable chunks. The final query is only intended to be run at the end. If you try to run the query before reaching the end of this guide you will likely get an error.

## Example: Combine and alias the tables

As a refresher, aliasing is when you temporarily name a table or column in your query to make it easier to read and write. To alias the **warehouse** and **orders** tables and join the tables, follow these steps. Remember, these statements require that you enter your unique individual project name or else they won't run. Be sure to substitute your project name in the code wherever you encounter **your-project** written. If you haven't explicitly assigned a project name, BigQuery generates one for you automatically. It typically looks like two words and a number, each separated by a hyphen, for example **august-west-100777**.

### Begin

with the **FROM** statement a few rows down. Later, you'll return to the top of the query to fill it in.

1. In row 3, enter **FROM your-project.warehouse\_orders.warehouse AS Warehouse**
2. In row 4, enter **LEFT JOIN your-project.warehouse\_orders.orders AS Orders**
3. In row 5, enter **ON Orders.warehouse\_id = Warehouse.warehouse\_id**

These statements will combine the two tables (**warehouse** and **orders**) using **warehouse\_id** as the common key (the column shared by both tables).

### Example: Organize your new table

Use the **GROUP BY** clause in SQL to group rows that have the same values in specified columns into aggregated data, such as sum, count, average, maximum, or minimum, based on the values in another column. This operation is particularly useful in databases where there is a need to analyze data based on certain criteria.

1. In row 6, enter **GROUP BY**
2. In row 7, enter **Warehouse.warehouse\_id,**
3. In row 8, enter **warehouse\_name**

Here, the combined table is grouped first by the warehouse ID and then by its name.

### Example: Build subquery logic

Now that you have the **FROM** statement and **JOIN**, go back up to the first lines and define the rows to select and operations to perform on them. From the objective, you know you want to return **five columns**: each warehouse's ID (**warehouse\_id**—column 1), state and alias (this info will be combined into a single column: **warehouse\_name**— column 2), and number of orders (**number\_of\_orders**—column 3); as well as the grand total of orders for all warehouses combined (**total\_orders**—column 4); and finally a column that classifies each warehouse by the percentage of grand total orders that it fulfilled: 0–20%, 21-60%, or > 60% (**fulfillment\_summary**—column 5).

Above everything you've written so far, write:

1. In row 1, enter **SELECT**
2. In row 2, enter **Warehouse.warehouse\_id,** \# (This is the first column.)
3. In row 3, enter **CONCAT(Warehouse.state, ': ', Warehouse.warehouse\_alias) AS warehouse\_name,** \# (This is the second column. Notice you're concatenating two existing columns into a new one)
4. In row 4, enter **COUNT(Orders.order\_id) AS number\_of\_orders,** \# (This is the third column.)
5. In row 5, enter **(SELECT COUNT(\*) FROM your-project.warehouse\_orders.orders AS Orders) AS total\_orders,** \# (This is the fourth column.)

To create the final column, you'll need to use a special keyword.

### Example: Create categories using **CASE**

Use the **CASE** keyword in SQL to create categories or group data based on specific conditions. This is valuable when dealing with numerical or textual data that needs to be segmented into different groups or categories for analysis, reporting, or visualization purposes.

For the final column, you'll use **CASE** to define which label to apply to each warehouse's fulfillment percentage (the percentage of the grand total of orders that it fulfilled). There will be three conditions, and thus three possible labels: "Fulfilled 0–20% of Orders", "Fulfilled 21–60% of Orders", or "Fulfilled more than 60% of Orders".

1. In row 6, enter **CASE**
2. In row 7, enter **WHEN COUNT(Orders.order\_id)/(SELECT COUNT(\*) FROM your-project.warehouse\_orders.orders AS Orders) <= 0.20** \# (This defines the first possible condition.)
3. In row 8, enter **THEN 'Fulfilled 0-20% of Orders'** \# (**THEN** defines the label to apply when the first condition is true.)
4. In row 9, enter **WHEN COUNT(Orders.order\_id)/(SELECT COUNT(\*) FROM your-project.warehouse\_orders.orders AS Orders) > 0.20** \# (This is the first part of the second condition.)
5. In row 10, enter **AND COUNT(Orders.order\_id)/(SELECT COUNT(\*) FROM your-project.warehouse\_orders.orders AS Orders) <= 0.60** \# (This is the second part of the second condition.)
6. In row 11, enter **THEN 'Fulfilled 21-60% of Orders'** \# (This defines the label to apply when the second condition is true.)
7. In row 12, enter **ELSE 'Fulfilled more than 60% of Orders'** \# (This defines the label to apply when neither of the first two conditions is true.)
8. In row 13, enter **END AS fulfillment\_summary** \# (The **END** keyword terminates the **CASE** declaration. Then the **AS** keyword indicates what the resulting column should be named.)

### Example: Filter using **HAVING**

Use the **HAVING** clause in SQL in combination with the **GROUP BY** clause to filter the results of aggregate functions in a query. While the **WHERE** clause filters individual rows *before* they are grouped, the **HAVING** clause filters groups of rows *after* they have been grouped. To filter out the warehouses that are currently being built (and therefore have no orders), enter the following lines below everything you've written so far:

1. In row 20, enter **HAVING**
2. In row 21, enter **COUNT(Orders.order\_id) > 0**

Here is the final query:

```sql
SELECT
  Warehouse.warehouse_id,
  CONCAT(Warehouse.state, ': ', Warehouse.warehouse_alias) AS warehouse_name,
  COUNT(Orders.order_id) AS number_of_orders,
  (SELECT COUNT(*) FROM your-project.warehouse_orders.orders AS Orders) AS total_orders,
  CASE
    WHEN COUNT(Orders.order_id)/(SELECT COUNT(*) FROM your-project.warehouse_orders.orders AS Orders) <= 0.20
    THEN 'Fulfilled 0-20% of Orders'
    WHEN COUNT(Orders.order_id)/(SELECT COUNT(*) FROM your-project.warehouse_orders.orders AS Orders) > 0.20
    AND COUNT(Orders.order_id)/(SELECT COUNT(*) FROM your-project.warehouse_orders.orders AS Orders) <= 0.60
    THEN 'Fulfilled 21-60% of Orders'
    ELSE 'Fulfilled more than 60% of Orders'
  END AS fulfillment_summary
FROM your-project.warehouse_orders.warehouse AS Warehouse
LEFT JOIN your-project.warehouse_orders.orders AS Orders
ON Orders.warehouse_id = Warehouse.warehouse_id
GROUP BY
  Warehouse.warehouse_id,
  warehouse_name
HAVING
  COUNT(Orders.order_id) > 0

```

## How do SQL functions function?

SQL functions help make data aggregation possible. As a refresher, data aggregation is the process of gathering data from multiple sources in order to combine it into a single, summarized collection. Take a moment to review some of these functions to better understand how to run these queries:

- **HAVING:** The **HAVING** clause filters the results of a SQL query based on conditions applied after the grouping. Check out [W3School’s HAVING overview](http://www-db.deis.unibo.it/courses/TW/DOCS/w3schools/sql/sql%5Fhaving.asp.html?ref=ksnk.media) for a tutorial on this clause
- **CASE: CASE** provides conditional logic in SQL queries, similar to an 'if-else' structure in programming languages. The [W3School’s CASE overview](https://www.w3schools.com/sql/sql%5Fcase.asp?ref=ksnk.media) explores the use of the **CASE** statement and how it works.
- **IF:** IF \*\*\*\*performs a simple conditional test and returns a value depending on the outcome. Review [W3School’s IF overview](https://www.w3schools.com/sql/func%5Fmysql%5Fif.asp?ref=ksnk.media) for a tutorial of the **IF** function and examples that you can practice with.
- **COUNT: COUNT** performs a simple conditional test and returns a value depending on the outcome. Though it seems simple, the **COUNT** function is just as important as all the rest. The [W3School’s COUNT overview](http://www-db.deis.unibo.it/courses/TW/DOCS/w3schools/sql/sql%5Ffunc%5Fcount.asp.html?ref=ksnk.media) provides a tutorial and examples.

## **Additional resources**

The following resources offer more guidance into subqueries and their usage:

- [**SQL subqueries**](https://www.w3resource.com/sql/subqueries/understanding-sql-subqueries.php?ref=ksnk.media): \*\*\*\*This detailed introduction includes the definition of a subquery, its purpose in SQL, when and how to use it, and what the results will be.
- [**Writing subqueries in SQL**](https://mode.com/sql-tutorial/sql-sub-queries/?ref=ksnk.media): Explore the basics of subqueries in this interactive tutorial, including examples and practice problems that you can work through.

As you continue to learn more about using SQL, functions, and subqueries, you will realize how much time you can truly save when memorizing these tips and tricks.