LAB 4: Group By, Having Clause and Oracle AGGRIGATE FUNCTION

Aggregate Functions

Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and inORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BYexpressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.


Using the GROUP BY Clause: Examples To return the minimum and maximum salaries for each department in the employees table, issue the following statement:
SELECT department_id, MIN(salary), MAX (salary)
     FROM employees
     GROUP BY department_id
   ORDER BY department_id;

To return the minimum and maximum salaries for the clerks in each department, issue the following statement:
SELECT department_id, MIN(salary), MAX (salary)
     FROM employees
     WHERE job_id = 'PU_CLERK'
     GROUP BY department_id
   ORDER BY department_id;
Using the GROUP BY CUBE Clause: Example To return the number of employees and their average yearly salary across all possible combinations of department and job category, issue the following query on the sample tables hr.employees and hr.departments:
SELECT DECODE(GROUPING(department_name), 1, 'All Departments',
      department_name) AS department_name,
   DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job_id,
   COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal"
   FROM employees e, departments d
   WHERE d.department_id = e.department_id
   GROUP BY CUBE (department_name, job_id)
   ORDER BY department_name, job_id;

DEPARTMENT_NAME                JOB_ID     Total Empl Average Sal
------------------------------ ---------- ---------- -----------
Accounting                     AC_ACCOUNT          1       99600
Accounting                     AC_MGR              1      144000
Accounting                     All Jobs            2      121800
Administration                 AD_ASST             1       52800
. . .
All Departments                ST_MAN              5       87360
All Departments                All Jobs          107  77798.1308
Using the GROUPING SETS Clause: Example The following example finds the sum of sales aggregated for three precisely specified groups:
  • (channel_desc, calendar_month_desc, country_id)
  • (channel_desc, country_id)
  • (calendar_month_desc, country_id)
Without the GROUPING SETS syntax, you would have to write less efficient queries with more complicated SQL. For example, you could run three separate queries and UNION them, or run a query with a CUBE(channel_desc, calendar_month_desc, country_id) operation and filter out five of the eight groups it would generate.
SELECT channel_desc, calendar_month_desc, co.country_id,
      TO_CHAR(sum(amount_sold) , '9,999,999,999') SALES$
   FROM sales, customers, times, channels, countries co
   WHERE sales.time_id=times.time_id 
      AND sales.cust_id=customers.cust_id 
      AND sales.channel_id= channels.channel_id 
      AND customers.country_id = co.country_id
      AND channels.channel_desc IN ('Direct Sales', 'Internet') 
      AND times.calendar_month_desc IN ('2000-09', '2000-10')
      AND co.country_iso_code IN ('UK', 'US')
  GROUP BY GROUPING SETS( 
      (channel_desc, calendar_month_desc, co.country_id), 
      (channel_desc, co.country_id), 
      (calendar_month_desc, co.country_id) );

CHANNEL_DESC         CALENDAR CO SALES$
-------------------- -------- -- --------------
Direct Sales         2000-09  UK      1,378,126
Direct Sales         2000-10  UK      1,388,051
Direct Sales         2000-09  US      2,835,557
Direct Sales         2000-10  US      2,908,706
Internet             2000-09  UK        911,739
Internet             2000-10  UK        876,571
Internet             2000-09  US      1,732,240
Internet             2000-10  US      1,893,753
Direct Sales                  UK      2,766,177
Direct Sales                  US      5,744,263
Internet                      UK      1,788,310
Internet                      US      3,625,993
                     2000-09  UK      2,289,865
                     2000-09  US      4,567,797
                     2000-10  UK      2,264,622
                     2000-10  US      4,802,459
Using the HAVING Condition: Example To return the minimum and maximum salaries for the employees in each department whose lowest salary is less than $5,000, issue the next statement:
SELECT department_id, MIN(salary), MAX (salary)
   FROM employees
   GROUP BY department_id
   HAVING MIN(salary) < 5000
   ORDER BY department_id;

DEPARTMENT_ID MIN(SALARY) MAX(SALARY)
------------- ----------- -----------
           10        4400        4400
           30        2500       11000
           50        2100        8200
           60        4200        9000

The following example uses a correlated subquery in a HAVING clause that eliminates from the result set any departments without managers and managers without departments:
SELECT department_id, manager_id 
   FROM employees 
   GROUP BY department_id, manager_id HAVING (department_id, manager_id) IN   (SELECT department_id, manager_id FROM employees x 
      WHERE x.department_id = employees.department_id)
   ORDER BY department_id;
Using the ORDER BY Clause: Examples To select all purchasing clerk records from employees and order the results by commission in descending order, issue the following statement:
SELECT * 
   FROM employees
   WHERE job_id = 'PU_CLERK' 
   ORDER BY commission_pct DESC; 

To select information from employees ordered first by ascending department number and then by descending salary, issue the following statement:
SELECT last_name, department_id, salary
   FROM employees
   ORDER BY department_id ASC, salary DESC; 

To select the same information as the previous SELECT and use the positional ORDER BY notation, issue the following statement, which orders by ascending department_id, then descending salary, and finally alphabetically by last_name:
SELECT last_name, department_id, salary 
   FROM employees 
   ORDER BY 2 ASC, 3 DESC, 1; 
The aggregate functions are:
Aggregate Example
The following example calculates the average salary of all employees in the hr.employees table:
SELECT AVG(salary) "Average" FROM employees;

 Average
--------
    6425
Analytic Example
The following example calculates, for each employee in the employees table, the average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee:
SELECT manager_id, last_name, hire_date, salary,
   AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date 
   ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
   FROM employees;

MANAGER_ID LAST_NAME                 HIRE_DATE     SALARY     C_MAVG
---------- ------------------------- --------- ---------- ----------
       100 Kochhar                   21-SEP-89      17000      17000
       100 De Haan                   13-JAN-93      17000      15000
       100 Raphaely                  07-DEC-94      11000 11966.6667
       100 Kaufling                  01-MAY-95       7900 10633.3333
       100 Hartstein                 17-FEB-96      13000 9633.33333
       100 Weiss                     18-JUL-96       8000 11666.6667
       100 Russell                   01-OCT-96      14000 11833.3333
Aggregate Examples
The following examples use COUNT as an aggregate function:
SELECT COUNT(*) "Total" FROM employees;

     Total
----------
       107

SELECT COUNT(*) "Allstars" FROM employees
   WHERE commission_pct > 0;

 Allstars
---------
       35

SELECT COUNT(commission_pct) "Count" FROM employees;

     Count
----------
        35

SELECT COUNT(DISTINCT manager_id) "Managers" FROM employees;

  Managers
----------
        18
Analytic Example
The following example calculates, for each employee in the employees table, the moving count of employees earning salaries in the range 50 less than through 150 greater than the employee's salary.
SELECT last_name, salary,
   COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING
      AND 150 FOLLOWING) AS mov_count FROM employees;

LAST_NAME                     SALARY  MOV_COUNT
------------------------- ---------- ----------
Olson                           2100          3
Markle                          2200          2
Philtanker                      2200          2
Landry                          2400          8
Gee                             2400          8
Colmenares                      2500         10
Patel                           2500         10
Aggregate Example
The following example determines the highest salary in the hr.employees table:
SELECT MAX(salary) "Maximum" FROM employees;
 
   Maximum
----------
      24000
Analytic Example
The following example calculates, for each employee, the highest salary of the employees reporting to the same manager as the employee.
SELECT manager_id, last_name, salary, 
   MAX(salary) OVER (PARTITION BY manager_id) AS mgr_max
   FROM employees;

MANAGER_ID LAST_NAME                     SALARY    MGR_MAX
---------- ------------------------- ---------- ----------
       100 Kochhar                        17000      17000
       100 De Haan                        17000      17000
       100 Raphaely                       11000      17000
       100 Kaufling                        7900      17000
       100 Fripp                           8200      17000
       100 Weiss                           8000      17000
Aggregate Example
The following statement returns the earliest hire date in the hr.employees table:
SELECT MIN(hire_date) "Earliest" FROM employees;
 
Earliest
---------
17-JUN-87
Analytic Example
The following example determines, for each employee, the employees who were hired on or before the same date as the employee. It then determines the subset of employees reporting to the same manager as the employee, and returns the lowest salary in that subset.
SELECT manager_id, last_name, hire_date, salary,
   MIN(salary) OVER(PARTITION BY manager_id ORDER BY hire_date
   RANGE UNBOUNDED PRECEDING) AS p_cmin
   FROM employees;

MANAGER_ID LAST_NAME                 HIRE_DATE     SALARY     P_CMIN
---------- ------------------------- --------- ---------- ----------
       100 Kochhar                   21-SEP-89      17000      17000
       100 De Haan                   13-JAN-93      17000      17000
       100 Raphaely                  07-DEC-94      11000      11000
       100 Kaufling                  01-MAY-95       7900       7900
       100 Hartstein                 17-FEB-96      13000       7900
       100 Weiss                     18-JUL-96       8000       7900
       100 Russell                   01-OCT-96      14000       7900
       100 Partners                  05-JAN-97      13500       7900
       100 Errazuriz                 10-MAR-97      12000       7900
Aggregate Example
The following example calculates the sum of all salaries in the sample hr.employees table:
SELECT SUM(salary) "Total"
     FROM employees;
 
     Total
----------
    691400
Analytic Example
The following example calculates, for each manager in the sample table hr.employees, a cumulative total of salaries of employees who answer to that manager that are equal to or less than the current salary. You can see that Raphaely and Cambrault have the same cumulative total. This is because Raphaely and Cambrault have the identical salaries, so Oracle Database adds together their salary values and applies the same cumulative total to both rows.
SELECT manager_id, last_name, salary,
   SUM(salary) OVER (PARTITION BY manager_id ORDER BY salary
   RANGE UNBOUNDED PRECEDING) l_csum
   FROM employees;

MANAGER_ID LAST_NAME           SALARY     L_CSUM
---------- --------------- ---------- ----------
       100 Mourgos               5800       5800
       100 Vollman               6500      12300
       100 Kaufling              7900      20200
       100 Weiss                 8000      28200
       100 Fripp                 8200      36400
       100 Zlotkey              10500      46900
       100 Raphaely             11000      68900
       100 Cambrault            11000      68900
       100 Errazuriz            12000      80900
. . .
       149 Taylor                8600      30200
       149 Hutton                8800      39000
       149 Abel                 11000      50000
       201 Fay                   6000       6000
       205 Gietz                 8300       8300
           King                 24000      24000

No comments:

Post a Comment

Popular Posts