Provide examples of common SQL aggregate functions and their use cases.

Question

Aryan Kumar · Answer

Certainly! Here are some common SQL aggregate functions and their typical use cases:

COUNT():

Use Case: Count the number of rows in a table or the number of rows that meet a specific condition.

SELECT COUNT(*) FROM employees;

SUM():

Use Case: Calculate the sum of values in a numeric column.

SELECT SUM(salary) FROM sales;

AVG():

Use Case: Calculate the average value of a numeric column.

SELECT AVG(rating) FROM product_reviews;

MAX():

Use Case: Find the maximum value in a column, often used to find the highest value.

SELECT MAX(price) FROM products;

MIN():

Use Case: Find the minimum value in a column, often used to find the lowest value.

SELECT MIN(stock_quantity) FROM inventory;

GROUP_CONCAT() (or equivalent functions like STRING_AGG() in SQL Server, LISTAGG() in Oracle):

Use Case: Concatenate values from multiple rows into a single string, useful for creating comma-separated lists.

SELECT department, GROUP_CONCAT(employee_name) AS employee_list
FROM employees
GROUP BY department;

SUM() with GROUP BY:

Use Case: Calculate the sum of values within groups, often used for group-level aggregations.

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

AVG() with HAVING:

Use Case: Calculate the average of a numeric column for groups and filter the results using the HAVING clause.

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

COUNT() with DISTINCT:

Use Case: Count the number of distinct values in a column, excluding duplicates.

SELECT COUNT(DISTINCT product_category) FROM products;

COUNT() with CASE:

Use Case: Count rows that meet specific conditions using a CASE statement within COUNT.

SELECT COUNT(CASE WHEN status = 'Completed' THEN 1 ELSE NULL END) AS completed_orders
FROM orders;

These aggregate functions are fundamental in SQL and are used extensively for summarizing and analyzing data in databases. Depending on your specific data analysis needs, you can choose the appropriate aggregate function to retrieve the desired information from your database tables.

forum

Provide examples of common SQL aggregate functions and their use cases.

Can you answer this question?

1 Answers