Group Functions in SQL

Group Functions:

Group functions also called as Aggregate functions in SQL. Below are the list of group functions.

1. MAX()

2.MIN()

3.AVG()

4.SUM()

5.COUNT(*)

6.COUNT(COLUMN NAME)

In all databases, Group functions are operate over number of values with in a table column and returns the single value.

1. MAX(): It returns the maximum value from a column.

syntax:      select MAX(Column name) from tablename;

Examples: select max(salary) from employee_table;   -->It returns maximum salary of employee.

                  select max(employee_name) from employee_table;  --> It returns max alphabet letter name.

2. MIN(): It returns the minimum value from a column.

syntax:       select MIN(column name) from tablename;

Examples:  select min(salary) from employee_table;  --> It returns the minimum salary of employee.

                   select min(employee_name) from employee_table; --> It returns min alphabet letter name.

Min alphabet = A

Max alphabet = Z

NOTE: In all databases, We are not allowed(oracle gives error) to use GROUP FUNCTIONS  in WHERE clause. 

Example: Select * from employee_table where salary=min(salary);

The above query gives error as "group function is not allowed here".

3. AVG(): It returns AVERAGE from number data type column.

Syntax:      Select AVG(column name) from tablename;

Examples: Select AVG(salary) from employee_table; --> It returns the average salary of the employees.

NOTE: In all databases by default all group functions ignores NULL values except COUNT(*) function.

If we want to count NULL values then we have to use NVL() functions within group function.

4.SUM(): It returns the total value from number data type column.

Syntax:       Select SUM(column name) from tablename;

Examples:  select SUM(SALARY) from employee_table; --> It returns the sum of salary of all employees.

5. COUNT(*) : It counts number of rows in a table.

Syntax:     Select COUNT(*) from table name;

Example: select COUNT(*) from employee_table;

6. COUNT(COLUMN NAME): It counts number of not NULL values in a column.

Example : Select Count(employee_name) from employee_table;  --> if any employee name is null then this function won't count that particular employee. It counts only not null values.

NOTE: We can also count distinct values from a column by using DISTINCT clause within a COUNT function.

Example: Select COUNT(DISTINCT(employee_number) from employee_table;

I will explain about GROUP BY and HAVING clauses in my next article.

If you have any doubts or suggestions, please drop a comment.                                                

                            Thank You.