Aggregating Data Using Group Functions

As part of learning sql,Here is good detail on Aggregating Data Using Group Functions

Group Functions unlike single value functions operate on set of of rows and return one rows per group.  The set of rows may be whole table or the table split into groups

Types of Group Functions include:

AVG([Distinct/all]  n) Numeric datatypes only. Average value of the column n ignoring null values
COUNT({*/[Distinct/all]expr}) It is only group function that includes null values. It count the number of rows in the selec t statement which satisfies the where clause.  Count(*) includes all the null and duplicate values
MAX([Distinct/all]  expr) It can use with any datatype. It gives maximum value of expr ignoring null values
MIN([Distinct/all]  expr) It can use with any datatype. . It gives minimum value of expr ignoring null values
STDDEV([Distinct/all]  n) Numeric datatypes only.It gives standard deviation of n ignoring null values
SUM ([Distinct/all]  n) Numeric datatypes only and cannot have other arithmetic operators in the provide the sum of n ignoring null values
VARIANCE([Distinct/all]  n) Numeric datatypes only.It gives variance of n ignoring null values



SELECT col1, col2, … col_n,

aggregate_function (aggregate_expression)

FROM tables

[WHERE conditions]

GROUP BY col1, col2, … col_n

Having  group condition;


Oracle server performed following steps

  • First the rows are selected based on where clause
  • Rows are grouped
  • The group function is applied to the each group
  • The group that matches the criterion in the having clause are displayed

So WHERE clause is evaluated first (restricts the query results), then the GROUP BY clause (groups the results of the WHERE), then the HAVING clause (further restricts the results, by restricting the groups returned).


Some Important points about group by

1) GROUP BY: Breaks down the results of group functions from one large table of data into smaller logical groupings.

2) WHERE clause cannot restrict a group use the HAVING clause.

3) Do not use the column alias in the GROUP BY clause.

4) HAVING: restricts the display of groups to those “having” the specified conditions.

5) The NVL function allows a GROUP BY function to include null values in its calculation.

6) Any column or expression in the select list that is not a aggregate function must be in the group by clause



Single Column

Select dept_id , avg(salary)   from emp  group by dept_id;


Multiple Column

Select dept_id ,job_id, sum(salary)   from emp  group by dept_id,job_id


Count function

SELECT dept, COUNT(*) AS “Np of employees”

FROM emp

WHERE sal < 15000

GROUP BY dept;


Min Function

SELECT dept, MIN(sal) AS “Lowest salary”

FROM emp