SQL Interview Questions

Aggregate functions in SQL perform calculations on a set of values and return a single value. They are commonly used with the GROUP BY clause to perform operations on groups of rows. Here are some of the most commonly used aggregate functions in SQL:

Common Aggregate Functions

1. COUNT()

   • Description: Returns the number of rows that match a specified condition.
   • Example:

     SELECT COUNT(*) FROM employees;

     SELECT COUNT(department_id) FROM employees WHERE salary > 50000;

2. SUM()

   • Description: Returns the total sum of a numeric column.
   • Example:

     SELECT SUM(salary) FROM employees;

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

3. AVG()

   • Description: Returns the average value of a numeric column.
   • Example:

     SELECT AVG(salary) FROM employees;

     SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

4. MIN()

   • Description: Returns the smallest value in a column.
   • Example:

     SELECT MIN(salary) FROM employees;

     SELECT department_id, MIN(salary) FROM employees GROUP BY department_id;

5. MAX()

   • Description: Returns the largest value in a column.
   • Example:

     SELECT MAX(salary) FROM employees;

     SELECT department_id, MAX(salary) FROM employees GROUP BY department_id;

Usage Examples

Suppose we have a table sales with the following structure:

sales
+----+---------+--------+-------+
| id | product | amount | year  |
+----+---------+--------+-------+
| 1  | A       | 100    | 2023  |
| 2  | B       | 150    | 2023  |
| 3  | A       | 200    | 2024  |
| 4  | B       | 100    | 2024  |
| 5  | C       | 50     | 2023  |
+----+---------+--------+-------+

Example 1: Using COUNT()

Count the number of sales records:

SELECT COUNT(*) AS total_sales FROM sales;

Result:

+-------------+
| total_sales |
+-------------+
| 5           |
+-------------+

Count the number of distinct products sold:

SELECT COUNT(DISTINCT product) AS unique_products FROM sales;

Result:

+----------------+
| unique_products|
+----------------+
| 3              |
+----------------+

Example 2: Using SUM()

Calculate the total sales amount:

SELECT SUM(amount) AS total_amount FROM sales;

Result:

+--------------+
| total_amount |
+--------------+
| 600          |
+--------------+

Calculate the total sales amount per product:

SELECT product, SUM(amount) AS total_amount
FROM sales
GROUP BY product;

Result:

+---------+--------------+
| product | total_amount |
+---------+--------------+
| A       | 300          |
| B       | 250          |
| C       | 50           |
+---------+--------------+

Example 3: Using AVG()

Calculate the average sales amount:

SELECT AVG(amount) AS average_amount FROM sales;

Result:

+----------------+
| average_amount |
+----------------+
| 120            |
+----------------+

Calculate the average sales amount per product:

SELECT product, AVG(amount) AS average_amount
FROM sales
GROUP BY product;

Result:

+---------+----------------+
| product | average_amount |
+---------+----------------+
| A       | 150            |
| B       | 125            |
| C       | 50             |
+---------+----------------+

Example 4: Using MIN() and MAX()

Find the smallest sales amount:

SELECT MIN(amount) AS min_amount FROM sales;

Result:

+-----------+
| min_amount|
+-----------+
| 50        |
+-----------+

Find the largest sales amount per product:

SELECT product, MAX(amount) AS max_amount
FROM sales
GROUP BY product;

Result:

+---------+-----------+
| product | max_amount|
+---------+-----------+
| A       | 200       |
| B       | 150       |
| C       | 50        |
+---------+-----------+

Summary

Aggregate functions are powerful tools in SQL that allow you to perform calculations on sets of values, providing insights and summaries that are crucial for data analysis and reporting.