SQL aggregation functions

SQL aggregation functions are used to perform calculations on sets of data in a database. These functions allow you to calculate the sum, average, minimum, maximum, and count of values in a database column. The most commonly used SQL aggregation functions are:

SUM(): This function calculates the sum of all values in a column. For example, if you have a table of sales data with a column named "amount," you can use the SUM() function to calculate the total amount of sales.

Example: SELECT SUM(amount) FROM sales_data;

AVG(): This function calculates the average of all values in a column. For example, if you have a table of test scores with a column named "score," you can use the AVG() function to calculate the average score.

Example: SELECT AVG(score) FROM test_scores;

MIN(): This function returns the minimum value in a column. For example, if you have a table of product prices with a column named "price," you can use the MIN() function to find the lowest-priced product.

Example: SELECT MIN(price) FROM products;

MAX(): This function returns the maximum value in a column. For example, if you have a table of product prices with a column named "price," you can use the MAX() function to find the highest-priced product.

Example: SELECT MAX(price) FROM products;

COUNT(): This function returns the number of rows in a table or the number of non-null values in a column. For example, if you have a table of customer data with a column named "name," you can use the COUNT() function to count the number of customers.

Example: SELECT COUNT(name) FROM customers;

COUNT(DISTINCT): This function returns the number of unique values in a column. For example, if you have a table of customer orders with a column named "product," you can use the COUNT(DISTINCT) function to count the number of unique products ordered.

Example: SELECT COUNT(DISTINCT product) FROM orders;

GROUP_CONCAT(): This function concatenates the values of a column for each group into a single string. For example, if you have a table of customer orders with columns for "customer_id" and "product," you can use the GROUP_CONCAT() function to concatenate the products ordered by each customer.

Example: SELECT customer_id, GROUP_CONCAT(product) FROM orders GROUP BY customer_id;

SUM(CASE WHEN): This function allows you to perform conditional aggregation by summing values based on a condition. For example, if you have a table of customer orders with columns for "product," "quantity," and "price," you can use the SUM(CASE WHEN) function to calculate the total revenue for each product.

Example: SELECT product, SUM(CASE WHEN quantity > 0 THEN quantity * price ELSE 0 END) AS revenue FROM orders GROUP BY product;

STDDEV(): This function calculates the standard deviation of all values in a column. For example, if you have a table of test scores with a column named "score," you can use the STDDEV() function to calculate the standard deviation of scores.

Example: SELECT STDDEV(score) FROM test_scores;

VARIANCE(): This function calculates the variance of all values in a column. For example, if you have a table of test scores with a column named "score," you can use the VARIANCE() function to calculate the variance of scores.

Example: SELECT VARIANCE(score) FROM test_scores;

FIRST(): This function returns the first value in a column. For example, if you have a table of customer orders with columns for "order_date" and "customer_id," you can use the FIRST() function to return the first order date for each customer.

Example: SELECT customer_id, FIRST(order_date) FROM orders GROUP BY customer_id;

LAST(): This function returns the last value in a column. For example, if you have a table of customer orders with columns for "order_date" and "customer_id," you can use the LAST() function to return the last order date for each customer.

Example: SELECT customer_id, LAST(order_date) FROM orders GROUP BY customer_id;

RANK(): This function assigns a rank to each row in a result set based on the values in a column. For example, if you have a table of product prices with columns for "product" and "price," you can use the RANK() function to assign a rank to each product based on its price.

Example: SELECT product, price, RANK() OVER (ORDER BY price DESC) AS rank FROM products;

DENSE_RANK(): This function assigns a dense rank to each row in a result set based on the values in a column. The difference between RANK() and DENSE_RANK() is that DENSE_RANK() does not skip any ranks if there are ties. For example, if you have a table of product prices with columns for "product" and "price," you can use the DENSE_RANK() function to assign a dense rank to each product based on its price.

Example: SELECT product, price, DENSE_RANK() OVER (ORDER BY price DESC) AS rank FROM products;

NTILE(): This function divides the rows in a result set into a specified number of groups and assigns a group number to each row. For example, if you have a table of employee salaries with a column named "salary," you can use the NTILE() function to divide the employees into four salary groups.

Example: SELECT employee_id, salary, NTILE(4) OVER (ORDER BY salary DESC) AS salary_group FROM employees;

These SQL aggregation functions can be used in combination with the GROUP BY clause to group data by one or more columns. For example, if you have a table of sales data with columns for "region," "year," and "amount," you can use the following query to calculate the total sales by region and year:

SELECT region, year, SUM(amount) FROM sales_data GROUP BY region, year;

I hope this explanation helps you understand SQL aggregation functions and how they can be used in SQL queries.