Explain any three aggregate functions with examples | iiQ8
Here are three commonly used aggregate functions in SQL, along with simple explanations and examples:
🔹 1. COUNT()
Purpose:
Returns the number of rows that match a specified condition.
Example:
SELECT COUNT(*) AS total_employees
FROM employees;
Explanation:
This query returns the total number of rows in the employees table.
🔹 2. SUM()
Purpose:
Returns the sum of values in a numeric column.
Example:
SELECT SUM(salary) AS total_salary
FROM employees
WHERE department = ‘Sales’;
Explanation:
This query returns the total salary paid to employees in the Sales department.
🔹 3. AVG()
Purpose:
Calculates the average value of a numeric column.
Example:
SELECT AVG(age) AS average_age
FROM employees;
Explanation:
This query returns the average age of all employees.
Summary Table:
| Function | Description | Example Use Case |
| COUNT() | Counts rows | Total number of users |
| SUM() | Adds up numeric values | Total sales for a month |
| AVG() | Calculates the average | Average customer rating |
SAP T-Code List for Procurement | iiQ8 T-Codes Inventory, and Material Management
Top 10 Interview Questions and Answers related to SQL Aggregate Functions, perfect for database, analyst, and backend developer roles:
Explain any three aggregate functions with examples | iiQ8
🔹 1. What are aggregate functions in SQL?
Answer:
Aggregate functions perform a calculation on a set of values and return a single value. They are used with GROUP BY to group results and include functions like COUNT(), SUM(), AVG(), MIN(), and MAX().
🔹 2. What is the difference between COUNT(*) and COUNT(column_name)?
Answer:
- COUNT(*) counts all rows, including those with NULLs.
- COUNT(column_name) counts only non-NULL values in that column.
🔹 3. How is the SUM() function used?
Answer:
SUM() returns the total of a numeric column.
Example:
SELECT SUM(salary) FROM employees;
🔹 4. What does the AVG() function return?
Answer:
AVG() returns the average of a numeric column’s values.
🔹 5. Can we use aggregate functions in WHERE clause?
Answer:
No. Aggregate functions cannot be used in WHERE. Use them in the HAVING clause after grouping.
🔹 6. What is the difference between WHERE and HAVING with aggregate functions?
Answer:
- WHERE filters before aggregation.
- HAVING filters after aggregation.
Example:
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
🔹 7. How do you find the maximum or minimum value in a column?
Answer:
- Use MAX(column_name) to get the highest value.
- Use MIN(column_name) to get the lowest value.
🔹 8. Can we use aggregate functions without GROUP BY?
Answer:
Yes. Aggregate functions can be used without GROUP BY to return a single value for the whole table.
Example:
SELECT COUNT(*) FROM orders;
🔹 9. What will be the result of COUNT(NULL)?
Answer:
COUNT(NULL) returns 0 because it ignores NULL values.
🔹 10. How do aggregate functions handle NULL values?
Answer:
Most aggregate functions ignore NULLs, except COUNT(*), which includes all rows regardless of NULLs.