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 function.it provide the sum of n ignoring null values |

VARIANCE([Distinct/all] n) | Numeric datatypes only.It gives variance of n ignoring null values |

**Syntax:**

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

**Examples:**

**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

GROUP BY dept

## Leave a Reply