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;