What are you looking for ?
Home » » Lesson (4) In Oracle & SQl Server 2008

Lesson (4) In Oracle & SQl Server 2008


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;
Share this article :

Post a Comment

Flag Counter

Social Profile

 
Copyright x 2011. By Wael Medhat - All Rights Reserved