SQL | Aggregate Functions

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.

#blog #interview #placement #learn #computer #science

21 views

        Contact Us

programmersdoor@gmail.com

  • LinkedIn
  • Facebook
  • Instagram

©2023 by Programmers Door