Aggregated Data Using the Group Functions in Oracle & Sql server 2008
Aggregated Data Using the Group Functions
After completing this lesson, you should be able to do the following:
• Identify the available group functions
• Describe the use of group functions
• Group data by using the GROUP BY clause
• Include or exclude grouped rows by using the HAVING clause
What Are Group Functions?
Group functions operate on sets of rows to give one result per group.
Types of Group Functions
§ AVG
§ COUNT
§ MAX
§ MIN
§ STDDEV
§ SUM
§ VARIANCE
SELECT [column,] group_function(column), ...
FROM table [WHERE condition] [GROUP BY column] [ORDER BY column];
SELECT AVG(episod_key)avg, MAX(episod_key)max ,
MIN(episod_key)min, SUM(episod_key)sum
FROM EPISODE
SELECT count ( PATIENT_ID)
FROM EPISODE
SELECT COUNT(DISTINCT PATIENT_ID)
FROM EPISODE
Group Functions and Null Values
§ Group functions ignore null values in the column
§ The NVL function forces group functions to include null values
§ SELECT AVG(episod_key)FROM EPISODE;
§ SELECT AVG(isnull (episod_key,0)) From Episode;
§ SELECT AVG(nvl (episod_key,0))FROM EPISODE;
Creating Groups of Data:
SELECT column, group_function(column) FROM table [WHERE condition]
[GROUP BY group_by_expression] [ORDER BY column];
SELECT PATIENT_ID,COUNT(episod_key) AS no_of_visits
FROM EPISODE
GROUP BY PATIENT_ID
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id,job_id ;
Note:
- Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause.
- You cannot use the WHERE clause to restrict groups.
- You use the HAVING clause to restrict groups.
- You cannot use group functions in the WHERE clause
GROUP BY department_idSELECT department_id,MAX(salary) FROM employees
HAVING MAX(salary)>10000 ;
SELECT job_id, SUM(salary) PAYROLL FROM employees
WHERE job_id NOT LIKE '%REP%' GROUP BY job_id
HAVING SUM(salary) > 13000 ORDER BY SUM(salary);
SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;
Post a Comment