Aggregate Functions, Group By and Having Clause
<< Functions And Operators - TOC - CSQL Join >>
This section outlines all the aggregate functions with group by and having clause.
Aggregate Functions
Aggregation function also called group function returns result based on groups of rows rather than on single rows. They are,
- COUNT: Produces the number of rows or non-NULL column values that the query selects.
- AVG: It produces the average (mean) of all selected values of a given column.
- MAX: Produces the largest of all selected values of a given column.
- MIN: Produces the smallest of all selected values of a given column.
- SUM: Produces the arithmetic sum of all selected values of a given column.
COUNT
It returns the number of rows that matches specified criteria and returns the number of values of the specified column except NULL values, which is integer data type value.
Syntax: COUNT({* | col_name })
Example: To find the total number of employees
SELECT COUNT(*) FROM emp;
AVG
It returns the average value of a numeric column. And it accepts the numeric field names to perform the query.
Syntax: AVG(col_name )
Example:To find the average salary of all employees
SELECT AVG (sal) FROM emp;
MAX
The MAX( ) function returns the largest value of the selected column and can be used with numeric, character, date, and time column. It returns a value same as expression and ignores any null values.
Syntax: MAX(col_name )
Example:To find the maximum salary paid to an employee
SELECT MAX (sal) FROM emp;
MIN
To calculate the minimum and smallest value present in a particular column the MIN function should be used. It also accepts the numeric and character in its arguments and ignores NULL values.
Syntax: MIN(col_name )
Example: To find the minimum salary paid to any employee
SELECT MIN (sal) FROM emp;
SUM
It returns total of values present in particular column, all the columns which form the argument to sum must be numeric only.
Syntax: SUM(col_name )
Example: To find the sum paid as salary to all employees every month
SELECT SUM(sal) FROM emp;
GROUP BY Clause
The GROUP BY clause is used to group selected rows and return a single row of summary information. Each group of rows is based on the values of the expression(s) specified in the GROUP BY clause. Grouping can be done on multiple columns and must be specified on the select query.
Syntax: GROUP BY <column list>
Example: To list the minimum salary of various categories of employee in various departments
SELECT deptno, job, MIN(sal) FROM emp GROUP BY deptno, job;
HAVING Clause
HAVING is associated with the group by clause and is used to apply search condition on the grouped rows. It removes the rows, which do not satisfy the criteria.
Syntax : HAVING <search condition>
Example: To find out Average, Maximum and Minimum salary of Departments where average salary is greater than 2000.
SELECT deptno, AVG(sal), MIN(sal), MAX(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 2000;
<< Functions And Operators - TOC - CSQL Join >>