Aggregation in SQL: Functions and Practical Examples

SQL stands for Structured Query Language. You can use this language to interact with relational databases. It provides a robust set of commands to manipulate and retrieve data. Among these commands, SQL aggregate functions play a crucial role in helping you summarize and analyze data efficiently.

This beginner-friendly guide will give you a detailed overview of SQL query aggregate functions, their general syntax, and various practical examples for better understanding.

What Is SQL Aggregation?

SQL aggregation is the process that involves using SQL queries to perform specific calculations on massive datasets and return a single value. You can implement it by leveraging functions such as COUNT(), SUM(), AVG(), MIN(), and MAX().

How to Use SQL Aggregation?

To use aggregates in SQL, you must write a query that applies one or more functions to the selected columns. These functions are used with SELECT statement’s GROUP BY and HAVING clauses that allow you to refine your query results by grouping data into subsets. An example of this can be calculating the total revenue generated in every region.

An important concept you should know before you begin using aggregations is column references. A column reference refers to the column within your table that holds the data you want to aggregate. For example, the code snippet to find the maximum salary drawn by the employees is as follows:

SELECT MAX(salary)
FROM employees;

Here, salary is the column reference, and employees is the table to which the column belongs.

Syntax for Aggregation and Grouping

The general syntax for SQL query aggregate function and grouping is outlined below:

SELECT aggregate_function_name(column_name), column1, column2, …
FROM table_name
WHERE condition
GROUP BY column1, column2, …
ORDER BY column1, column2, … ;

Let’s break this down:

• SELECT: It mentions the columns you want to retrieve to perform aggregate functions and other data operations.‍
• aggregate_fucntion_name(column_name): This returns the result of the aggregate function applied to the specified column. If you include DISTINCT within the parentheses, the outcome will consider unique data values; otherwise, all records will be taken into account.
• ‍FROM table_name: It states the names of the tables from which data will be used or manipulated.
• ‍WHERE: With this clause, you can filter the rows based on a condition before applying aggregation.‍
• GROUP BY column1, column2, ...: You can categorize data points with similar characteristics into different groups and implement SQL aggregation functions on each one of them.‍
• ORDER BY column1, column2, …: It enables you to sort the result set and is typically used after aggregation.

A practical example of aggregation and grouping can make it easier for you to utilize this syntax. Consider a table named sales with product, units, and location columns, as shown in the image below.

Your SQL query to identify the total quantity of each product sold in each location will be:

SELECT product, location, SUM(units) AS total_quantity
FROM sales
GROUP BY location, product
ORDER BY location product;

The SELECT clause lets you fetch required columns, such as product and location. It also calculates the sum of units and returns that number using total_quantity as an alias.

‍Note: AS is used as a keyword to give the summarized column a name (alias).‍

The GROUP BY clause ensures that the summation of product units is done separately for each unique combination of product and location. Lastly, the ORDER BY clause sorts the result set in ascending order by location first and then by product within each location.

Most Popular SQL Aggregation Functions

SQL offers several built-in aggregation functions. You can use them to extract insights from your data and streamline various business decisions. The most commonly used SQL query aggregate functions are:

• COUNT(): Returns the total number of rows in a dataset.‍
• SUM(): Calculates the total sum of a column after grouping.‍
• AVG(): Finds the average of a column after grouping.‍
• MIN(): Provides the smallest value in a group.‍
• MAX(): Returns the largest value present in a group.

‍Note: All SQL aggregate functions except COUNT ignore the null values to provide results.

SQL Aggregation Functions with Examples

SQL aggregate functions help you process multiple records with similar data and support your business intelligence efforts. In this section, you will find a simplified explanation of how to execute aggregates in SQL, along with examples. However, if you want to learn about advanced SQL concepts, you can refer to this article.

#1 COUNT()

The general syntax of COUNT() is  COUNT(*) or COUNT(DISTINCT cloumn_name) or COUNT(column_name).

‍Note: When you use an asterisk (*), the COUNT function will consider null values as well. Conversely, if you mention a column name, it will leave out null values.

‍Example: An SQL query to count the number of employees present in the marketing department of an organization.

SELECT COUNT(employee_id) AS “Sales Employees” 
FROM employees
WHERE LOWER(department) = ‘marketing’

In this example, the alias name is written in double quotes because there is space between the words. Additionally, the LOWER function converts both the department column and the string marketing to lowercase before comparison, making it case-insensitive. This enables you to filter your rows first based on the condition and provide an accurate count of employees in the marketing department.

#2 SUM()

The general syntax of SUM() is SUM(column_name). You can only use it on columns containing numerical data points. The aggregate function allows you to include an expression as a parameter, widening the range of operations you can perform.

‍Example: An SQL query to calculate the salary of an employee belonging to sales and marketing departments after a 10% increment.

SELECT department, SUM(salary * 1.1) AS new_salary
FROM employees 
WHERE department IN (‘sales’, ‘marketing’)
GROUP BY department;

You can use most SQL aggregate functions with an expression as long as it’s compatible with the function and can produce a result.       

#3 AVG()

The general syntax of AVG() is AVG(column_name). Similar to the SUM(), this function works only on numerical columns. You can leverage it to find the average of the selected group of values and extend its functionality by using it in a subquery.

‍Example: An SQL query to list customers that have an average order total (expenditure) greater than the average of total sales.

SELECT customer_id, AVG(order_total) AS avg_order_total 
FROM customers 
GROUP BY customer_id 
HAVING avg_order_total > (SELECT AVG(order_total) FROM customers);

#4 MIN() and MAX()

The general syntax of MIN() is MIN(column_name), and MAX() is MAX(column_name). These functions are versatile and can be applied to various data types, including date, integer, or text columns.

With numerical data, MIN() returns the lowest, and MAX() returns the highest value. For columns containing dates, the former provides the earliest recorded date and the latter with the latest recorded date. Lastly, for textual data, MIN() retrieves data that comes first alphabetically (closest to ‘A’), and MAX() retrieves data that is closest to ‘Z.’

‍Example: An SQL query to identify the cheapest and the most expensive products in each category.

SELECT MIN(unit_cost) AS Cheapest, MAX(unit_cost) AS Expensive, category_id 
FROM products
GROUP BY category_id;

Streamlined SQL Aggregation with Airbyte

Airbyte is an AI-powered data integration platform that simplifies your SQL aggregation workflows by automating data ingestion pipelines. It offers over 550 pre-built connectors and a user-friendly interface to help you build data pipelines effortlessly. With these connectors, you can extract high-volume, high-velocity data from multiple sources simultaneously and load it into an SQL database or other destinations of your choice. This process ensures that your data is centralized, accurate, and ready for aggregation into your SQL environment.

Some more features of Airbyte that can enhance SQL aggregation workflows include:

• AI-Assisted Custom Connector Development: Airbyte gives you the flexibility to create your own connectors through Connector Builder and Connector Development Kits (CDKs). The AI assistant available in Connector Builder gives intelligent suggestions and pre-fils most configuration fields, reducing the time needed to set up pipelines.
• ‍Developer-Friendly Pipelines: With PyAirbyte, you can utilize ETL and SQL together to transform, analyze, and manage your data assets smoothly. It offers a set of utilities for using Airbyte connectors in Python. You can use PyAirbyte to extract data from several sources and load it to various SQL caches, such as DuckDB. This cached data is compatible with SQL-based tools, Python libraries (Pandas), and AI frameworks like LangChain and LlamaIndex (to build LLM-powered applications).
• ‍Data Transformations: You can load semi-structured, structured, and unstructured data into your preferred destination, such as an SQL database, warehouse, lake, or vector store. Airbyte helps you transform this data by implementing RAG techniques (automatic chunking, indexing, embedding) and complex SQL queries (through dbt Cloud integration).‍
• Refreshes: You can employ refreshes to synchronize your data and keep it up-to-date without any downtime. Airbyte allows you to perform incremental syncs and full refreshes. The former lets you append your data, while the latter supports complete data overwrites.

Airbyte has also announced the general availability of the Self-Managed Enterprise edition. This version provides large-scale data ingestion capabilities and PII masking to prevent mishandling of your sensitive data.

To learn more about the tool, you can read the official documentation or contact the experts at Airbyte.

Wrapping It Up

SQL aggregate functions are essential for analyzing data. By understanding how these functions work, you can easily implement SQL-based queries to perform tasks like summarization, grouping, and extracting meaningful insights from large datasets. This also speeds up downstream tasks like reporting and data-driven decision-making, saving time and effort in the long run.