Question: Explain Aggregate functions with example.
An aggregate function allows you to perform a calculation on a set of values to return a single scalar value. We often use aggregate functions with the GROUP BY and HAVING clauses of the SELECT statement.
The following are the most commonly used SQL aggregate functions:
- COUNT – Counts rows in a specified table or view.
- SUM – Calculates the sum of values.
- AVG – Calculates the average of a set of values.
- MIN – Gets the minimum value in a set of values.
- MAX – Gets the maximum value in a set of values.
| Sid | SName | Marks |
|---|---|---|
| 1 | Sahir | 90 |
| 2 | Vishal | 80 |
| 3 | Sahira | 89 |
| 4 | Hir | 99 |
| 5 | Vruta | 88 |
| 6 | Dev | 90 |
1. COUNT()
- This function is used to calculate number of rows in a table selected by query.
- COUNT returns the number of rows in the table when the column value is not NULL.
Example:
- Find total number of students
- SELECT COUNT(Sid) as COUNT
- FROM Exam_Marks
| Count |
|---|
| 6 |
2. SUM()
- This function is used to calculate sum of column values in a table selected by query.
Example:
- Find total of marks scored by all students
- SELECT SUM(Marks) as SUM
- FROM Exam_Marks
| Sum |
|---|
| 446 |
3. AVG()
- This function is used to calculate the average of column values in a table selected by query.
- This function first calculates sum of columns and then divide by total number of rows.
Example:
- Find average marks of students
- SELECT AVG(Marks) as AVG
- FROM Exam_Marks
| AVG |
|---|
| 89.33 |
4. MIN()
- This function is used to find maximum value out of column values in a table selected by query.
Example:
- Find total of marks scored by all students
- SELECT MIN(Marks) as MIN
- FROM Exam_Marks
| MIN |
|---|
| 80 |
5. MAX()
- This function is used to find maximum value out of column values in a table selected by query.
Example:
- Find total of marks scored by all students
- SELECT MAX(Marks) as MAX
- FROM Exam_Marks
| MAX |
|---|
| 99 |
