MS SQL Server Aggregate function
MS SQL Server MIN Function
- MIN Function is a type of aggregate function used to find a minimum record among specified columns.
- It is a built-in function used with the WHERE, GROUP BY, ORDER BY, and HAVING Clause.
- The MIN function ignores NULL value
- Syntax:
SELECT MIN(DISTINCT expression) FROM table_name(s)[WHERE conditions];
Example:
SELECT MIN(CGPA) AS 'Minimum CGPA' FROM student;
Output:
| Minimum CGPA |
| 7.25 |
Example:
SELECT Name, Branch CGPA FROM student WHERE CGPA = (SELECT MIN(CGPA) AS "Minimum CGPA" FROM student);
Output:
| Name | Branch | CGPA |
| Aayush | CSE | 7.25 |
MS SQL Server MAX Function
- MAX Function is a type of aggregate function used to find a maximum record among specified columns.
- It is a built-in function used with the WHERE, GROUP BY, ORDER BY, and HAVING Clause.
- The MAX function ignores NULL values.
- Syntax:
SELECT MAX(DISTINCT expression) FROM table_name(s)[WHERE conditions];
Example:
SELECT MAX(CGPA) AS 'Maximum CGPA' FROM student;
Output:
| Maximum CGPA |
| 9.86 |
Example:
SELECT Name, Branch CGPA FROM student WHERE CGPA = (SELECT MAX(CGPA) AS "Maximum CGPA" FROM student);
Output:
| Name | Branch | CGPA |
| Ravi | CSE | 9.86 |
MS SQL Server SUM Function
- SUM Function is a type of aggregate function used to calculate the total summed value of specified columns.
- It is a built-in function used with the WHERE, GROUP BY, ORDER BY, and HAVING Clause.
- Syntax:
SELECT SUM(DISTINCT aggregate_expression)FROM table_name(s)[WHERE conditions];
Example:
SELECT SUM(CGPA) AS "Total CGPA" FROM student;
Output:
| Total CGPA |
| 33.84 |
Example:
SELECT SUM(CGPA) AS "Total CGPA" FROM student WHERE Age>=19;
Output:
| Total CGPA |
| 33.84 |
MS SQL Server AVG Function
- AVG Function is a type of aggregate function used to calculate the average value of specified columns.
- It is a built-in function used with the WHERE, GROUP BY, ORDER BY, and HAVING Clause.
- The AVG function ignores NULL values.
- Syntax:
SELECT AVG(ALL | DISTINCT expression) FROM table_name(s) [WHERE conditions];
Example:
SELECT AVG(CGPA) AS "Average CGPA" FROM student;
Output:
| Average CGPA |
| 8.46 |
Example:
SELECT AVG(CGPA) AS "Average CGPA" FROM student WHERE Branch='CSE';
Output:
| Average CGPA |
| 8.46 |
MS SQL Server COUNT Function
- COUNT Function is a type of aggregate function used to calculate the total number of rows in table
- It is a built-in function used with the WHERE, GROUP BY, ORDER BY, and HAVING Clause.
- The COUNT function does not ignore NULL values.
- Syntax:
SELECT COUNT(expression) FROM table_name(s)[WHERE conditions];
Example:
SELECT COUNT(*) AS "Total Student" FROM student;
Output:
| Total Student |
| 4 |
