In database management an aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning.
Various Aggregate Functions
1) Count() 2) Sum() 3) Avg() 4) Min() 5) Max()
Now let us understand each Aggregate function with a example:
Id Name Salary ----------------------- 1 A 80 2 B 40 3 C 60 4 D 70 5 E 60 6 F Null
Count(*): Returns total number of records .i.e 6.
Count(salary): Return number of Non Null values over the column salary. i.e 5.
Count(Distinct Salary): Return number of distinct Non Null values over the column salary .i.e 4
sum(salary): Sum all Non Null values of Column salary i.e., 310
sum(Distinct salary): Sum of all distinct Non-Null values i.e., 250.
Avg(salary) = Sum(salary) / count(salary) = 310/5
Avg(Distinct salary) = sum(Distinct salary) / Count(Distinct Salary) = 250/4
Min(salary): Minimum value in the salary column except NULL i.e., 40.
Max(salary): Maximum value in the salary i.e., 80.
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.
- SQL | Functions (Aggregate and Scalar Functions)
- Aggregate functions in Cassandra
- SQL | Difference between functions and stored procedures in PL/SQL
- SQL | Date functions
- Categories of SQL Functions
- Difference between T-SQL and PL-SQL
- Difference between SQL and T-SQL
- Functions of Distributed Database System
- Characteristics and Functions of Data warehouse
- MySQL | Ranking Functions
- UUID and Timeuuid functions in Cassandra
- Additional Functions in CQL (Cassandra Query Language)
- MINUTE(), MICROSECOND() and HOUR() functions in MySQL
- SQL | SELECT Query
- SQL | Distinct Clause
- SQL | WHERE Clause
- SQL | AND and OR operators
- SQL | INSERT INTO Statement
- SQL | DELETE Statement
- SQL | UPDATE Statement