SQL Aggregation

Aggregation in SQL

Aggregation in SQL is a process that involves summarizing data from multiple rows into a single result. This is commonly done using aggregate functions. These functions perform calculations on a set of values and return a single value. Aggregate functions are particularly useful for generating summary statistics and insights from data, such as counts, totals, averages, and more.

Aggregate functions are often used in conjunction with the GROUP BY clause, which groups rows that have the same values in specified columns into summary rows. The HAVING clause is then used to filter the results based on conditions involving the aggregate functions.

Common Aggregate Functions

• COUNT() - Counts the number of rows.

  SELECT COUNT(*) FROM employees;

• SUM() - Calculates the total sum of a numeric column.

  SELECT SUM(salary) FROM employees;

• AVG() - Calculates the average value of a numeric column.

  SELECT AVG(salary) FROM employees;

• MIN() - Finds the minimum value in a column.

  SELECT MIN(salary) FROM employees;

• MAX() - Finds the maximum value in a column.

  SELECT MAX(salary) FROM employees;

GROUP BY Clause

The GROUP BY clause groups rows that have the same values in specified columns into summary rows. Here's an example:

SELECT department, SUM(salary)
FROM employees
GROUP BY department;

This query calculates the total salary paid for each department.

HAVING Clause

The HAVING clause is used to filter groups based on conditions involving aggregate functions. It is similar to the WHERE clause, but it applies to groups of rows rather than individual rows.

SELECT department, SUM(salary) as total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000;

This query first groups the employees by department, calculates the total salary for each department, and then filters out the departments where the total salary is 100,000 or less.

Example: Aggregation with GROUP BY and HAVING

Suppose you have a table sales with the following columns: product_id, store_id, quantity, and sale_amount. Here’s how you can use aggregation, GROUP BY, and HAVING:

Total Sales per Product

SELECT product_id, SUM(sale_amount) as total_sales
FROM sales
GROUP BY product_id;

Average Quantity Sold per Store

SELECT store_id, AVG(quantity) as average_quantity
FROM sales
GROUP BY store