Aggregating Data Using Group Functions

Aggregating Data Using Group Functions

LECTURE 10 Aggregating Data Using Group Functions ?What Are Group Functions Group functions operate on sets of rows to give one result per

group. Types of Group Functions Description Group Function Average value of n, ignoring null values

AVG ([DISTINCT|ALL] n) Number of rows (count all selected rows using *, including duplicates and rows with nulls) COUNT ({*| [DISTINCT| ALL] expr}) Maximum value of expr,

ignoring null values MAX([DISTINCT|ALL] expr) Minimum value of expr, ignoring null values MIN([DISTINCT|ALL] expr) Sum values of n, ignoring null

values SUM( [DISTINCT|ALL] n) Group Functions Syntax SELECT [column,] group_function(column), ... FROM table [WHERE condition] [GROUP BY column]

[ORDER BY column]; Guidelines for Using Group Functions DISTINCT makes the function consider only non-duplicate values; ALL makes it consider every value including duplicates. The default is ALL and therefore does not need

to be specified. The data types for the functions with an expr argument may be CHAR, VARCHAR2, NUMBER, or DATE. With n argument can be only NUMBER. All group functions ignore null values..

Using the AVG and SUM Functions Using the MIN and MAX Functions Using the MIN and MAX Functions You can use the MIN and MAX functions for any data

type. The previews slide example displays the most junior and most senior employee. The following example displays the employee last name

that is first and the employee last name that is the last in an alphabetized list of all employees. SELECT MIN(last_name), MAX(last_name) FROM employees; Using the MIN and MAX Functions Note:

AVG, SUM functions can be used only with numeric data types. Using the COUNT Function Using the COUNT Function Using the COUNT

Function( Example) Using the DISTINCT Keyword Group Functions and Null Values Group functions ignore null values in the column. For example:

SELECT AVG(commission_pct) FROM employees; The average is calculated as the total commission paid to all employees divided by the number of employees receiving a commission. Creating Groups of Data

Until now, all group functions have treated the table as one large group of information. At times, you need to divide the table of information into smaller groups. This can be done by using the GROUP BY clause . Creating Groups of Data: GROUP BY Clause Syntax Guidelines If

you include a group function in a SELECT clause, you cannot select individual results as well, unless the individual column appears in the GROUP BY clauseYoull receive an error message if you fail to include the column list in the GROUP BY clause. Using a WHERE clause, you can exclude rows before

dividing them into groups executed first (before group by) You cannot use a column alias in the GROUP BY clause . By

default, rows are sorted by ascending order of the columns included in the GROUP BY list. You can override this by using the ORDER BY clause. Using the GROUP BY Clause Using the GROUP BY Clause Grouping by More Than One Column Sometimes

you need to see results for groups within groups. Example 1 shows a report that displays the total salary being paid to each job title, within each department. Example 1

Example 1 The EMPLOYEES table is grouped first by department number and then, within that grouping, by job title. For example, the four stock clerks in

department 50 are grouped together and a single result (total salary) is produced for all stock clerks within the group. Illegal Queries Using Group Functions Any

column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause. SELECT department_id, COUNT(last_name) FROM employees; Column missing in the GROUP BY clause SELECT department_id, COUNT(last_name) * ERROR at line 1:

ORA-00937: not a single-group group function Illegal Queries Using Group Functions 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.

SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id; Error * You can correct the error by using the HAVING clause to restrict groups. SELECT department_id, AVG(salary) FROM employees HAVING AVG(salary) > 8000

GROUP BY department_id; Excluding Group Results: The HAVING Clause Use the HAVING clause to restrict groups: 1. Rows are grouped. 2. The group function is applied.

3. Groups matching the HAVING clause are displayed. SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]; Using the HAVING Clause

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);

Recently Viewed Presentations

  • Uses of Quasi-Isochronous Helical Channels in the Front

    Uses of Quasi-Isochronous Helical Channels in the Front

    Dave Neuffer Katsuya Yonehara ... To transform a cigar εTxε║ into a football, we strive to have emittance exchange from longitudinal to transverse at a rate that can be cooled transversely, netting zero emittance growth transversely and cooling longitudinally. So,...
  • Fomento de sinergias en favor de los pastizales

    Fomento de sinergias en favor de los pastizales

    Then there is the Grasslands Beneficial Management Practices online tool, a resource that makes universally accessible 94 beneficial management practices designed for North American ranchers, conservation organizations, and government and academic institutions.
  • Epitelové nádory ledvin - Univerzita Karlova

    Epitelové nádory ledvin - Univerzita Karlova

    Nádory ledvin Nádory ledvin z renálních bunĕk (epitelové) mezenchymové smíšené epitelové a mezenchymové neuroendokrinní hematopoetické a lymfogenní germinální sekundární (metastatické) dětského věku dospělých benigní maligní epitelové mezenchymové neuroendokrinní hematopoetické a lymfogenní melanocytární benigní maligní Nádory ledvinného parenchymu Nádory ...
  • Unit 1 - lee.k12.nc.us

    Unit 1 - lee.k12.nc.us

    6th Grade UBD - Unit 1 - The World In Spatial Terms. Preview. ... Legend- The legend, or key, tells the user about the symbols used on the map. Key Term. Longitude- The series of imaginary lines that run north...
  • The History of Premillennialism:

    The History of Premillennialism:

    In the primitive period they were held together in a naïve, unreflective fashion, with little or no attempt to work out their or solve the problems they raise." ~ J.N.D. Kelley Premillennial Observations: A. It has long been established by...
  • House Study Commission

    House Study Commission

    Perceptions regarding the counselors role in mental health situations in the school setting. Provide responsive services including internal and external referral procedures, short-term counseling or crisis intervention focused on mental health or situational (e.g. grief, difficult transitions, self-harm, etc.) 1064...
  • Speed Teaching: - Brigham Young University-Idaho

    Speed Teaching: - Brigham Young University-Idaho

    No matter how wicked and ferocious and depraved the Lamanites might be (and they were that!), no matter by how much they outnumbered the Nephites, darkly closing in on all sides, no matter how insidiously they spied and intrigued and...
  • ACT Power Plus Vocabulary - calhoun.k12.al.us

    ACT Power Plus Vocabulary - calhoun.k12.al.us

    Synonyms charisma spirit saturnine - (adj.) gloomy; sluggish The hostess's saturnine attitude caused the party to end early. Synonyms sullen morose Antonyms genial endemic - (adj.) confined to a particular country or area Once it had been endemic to Africa,...