.png)
An aggregate function allows you to perform a calculation on a set of values to return a single scalar value. It returns a single value. We often use aggregate functions with the GROUP BY and HAVING clauses of the SELECT statement.
Types of SQL Aggregation Function-
All aggregate functions above ignore NULL values except for the COUNT function.
1. COUNT FUNCTION-
COUNT function is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types.COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table. COUNT(*) considers duplicate and Null.
COUNT() Syntax-COUNT(*) or COUNT( [ALL|DISTINCT] expression )
Examples :
COUNT()-
SELECT COUNT(*)FROM product_mast;
COUNT() with WHERE-
SELECT COUNT(*)FROM product_mast WHERE rate>=20;
COUNT() with DISTINCT-
SELECT COUNT(DISTINCT company)FROM product_mast;
COUNT() with GROUP BY-
SELECT company, COUNT(*)FROM product_mast GROUP BY company;
COUNT() with HAVING-
SELECT company, COUNT(*) FROM product_mast GROUP BY company HAVING COUNT(*)>2;2. SUM Function-
Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.
SUM() Syntax-SUM() or SUM( [ALL|DISTINCT] expression )
Example :
SUM()-
SELECT SUM(cost)FROM product_mast;
SUM() with WHERE-
SELECT SUM(cost)FROM product_mast WHERE qty>3;
SUM() with GROUP BY-
SELECT SUM(cost) FROM product_mast WHERE qty>3 GROUP BY company;
SUM() with HAVING-
SELECT company, SUM(cost) FROM product_mast GROUP BY company HAVING SUM(cost)>=170;3. AVG function-
The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values.
AVG() Syntax-AVG() or AVG( [ALL|DISTINCT] expression )
Example :
AVG()-
SELECT AVG(cost) FROM product_mast;
AVG() with HAVING-
SELECT company, AVG(cost) FROM product_mast GROUP BY company HAVING AVG(cost)>=65;4. MAX Function-
MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.
MAX() Syntax-MAX() or MAX( [ALL|DISTINCT] expression )
Example :
MAX()-
SELECT MAX(rate) FROM product_mast;
MAX() with HAVING-
SELECT company, MAX(rate) FROM product_mast GROUP BY company HAVING MAX(rate)=30; 5. MIN Function-
MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column.
MIN() Syntax-MIN() or MIN( [ALL|DISTINCT] expression )
Example :
MIN()-
SELECT MIN(rate) FROM product_mast;
MIN() with HAVING-
SELECT company, MIN(rate) FROM product_mast GROUP BY company HAVING MIN(rate)<20;Happy Coding!
Follow us on Instagram @programmersdoor
Join us on Telegram @programmersdoor
Please write comments if you find any bug in above code/algorithm, or find other ways to solve the same problem.
Follow Programmers Door for more.