Skip to content

Click to use (opens in a new tab)

What is Aggregation

Introduction to Aggregation in Database Systems

Aggregation involves summarizing data to provide insights and facilitate analysis. It typically groups data based on one or more columns and applies aggregate functions like SUM(), COUNT(), AVG(), MIN(), and MAX().

Common Aggregate Functions

SUM()

Calculates the sum of values in a specified column.

Example: Calculating Total Sales

SELECT SUM(sales_amount) AS total_sales
FROM sales;

COUNT()

Counts the number of items in a group.

Example: Counting Orders

SELECT COUNT(order_id) AS total_orders
FROM orders;

AVG()

Computes the average value of a numeric column.

Example: Average Product Price

SELECT AVG(price) AS avg_price
FROM products;

MIN()

Finds the minimum value in a specified column.

Example: Finding the Lowest Salary

SELECT MIN(salary) AS lowest_salary
FROM employees;

MAX()

Finds the maximum value in a specified column.

Example: Finding the Highest Salary

SELECT MAX(salary) AS highest_salary
FROM employees;

Grouping Data with GROUP BY

Groups data based on one or more columns for computing aggregate values.

Example: Total Sales by Region

SELECT region, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region;

Filtering Aggregated Results with HAVING

Applies conditions to groups after aggregation.

Example: Regions with Total Sales Greater Than $10,000

SELECT region, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region
HAVING SUM(sales_amount) > 10000;

Combining Aggregations with Other Operations

Example: Using Subqueries for Aggregation

Finds the top-performing product within each category.

SELECT category, product_name, sales_amount
FROM (
    SELECT category, product_name, sales_amount,
           RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) as rank
    FROM sales
) ranked_sales
WHERE rank = 1;

Chat2DB - AI Text2SQL Tool for Easy Database Management

Click to use (opens in a new tab)

What can Chat2DB do?