Orange%20Coast%20College%20Business%20Division%20-%20CS/CIS%20Department%20Fall%202003%20CIS%20183%20

1
Orange Coast CollegeBusiness Division - CS/CIS
DepartmentFall 2003 CIS 183 Oracle SQL
Programming

• Instructor
• Dr. Martha Malaty
• Text Original Presentations
• Lannes L. Morris-Murphy
• Oracle 9i SQL with an Introduction to PL/SQL,
  2003

2
Chapter 6Group Functions
3
Chapter Objectives

• Differentiate between single-row and multiple-row
  functions
• Use the SUM and AVG functions for numeric
  calculations
• Use the COUNT function to return the number of
  records containing non-NULL values
• Use COUNT() to include records containing NULL
  values
• Use the MIN and MAX functions with non-numeric
  fields
• Determine when to use the GROUP BY clause to
  group data

4
Chapter Objectives

• Identify when the HAVING clause should be used
• List the order of precedence for evaluating
  WHERE, GROUP BY, and HAVING clauses
• State the maximum depth for nesting group
  functions
• Nest a group function inside a single-row
  function
• Calculate the standard deviation and variance of
  a set of data, using the STDDEV and VARIANCE
  functions

5
Group Functions

• Also called multiple-row or aggregate functions
• Operate on sets of rows
• Set of rows could be a complete table or grouped
  part of a table
• Each function has one input argument and returns
  one result for each group of rows processed
• Mainly used in the SELECT clause

6
Group Functions

• Group functions types
• SUM
• AVG
• COUNT
• MIN
• MAX
• STDDEV
• VARIANCE

7
Group Functions

• Syntax
• SELECT column_name, group_function
  (DISTINCTALL column_name), ...
• FROM table_name
• WHERE condition
• GROUP BY column_name
• ORDER BY column_name

8
Group Functions

• Almost any combination of built-in functions is
  allowed by SQL

Function Input argument Value returned
SUM(DISTINCT ALL col) col column or expression Sum of values of column or expression
AVG(DISTINCT ALL col) col column Average value of col
COUNT(ALL ) None Number of rows including duplicates and NULLS
COUNT(DISTINCT ALL col) col column or expression Number of rows where the value is not NULL
MAX(DISTINCT ALL col) col column or expression The maximum value in the column or expression
MIN(DISTINCT ALL col) col column or expression The minimum value in the column or expression
STDEV(DISTINCT ALL col) col column or expression The standard deviation of the column or expression, ignoring NULL values
VARIANCE(DISTINCT ALL col) col column or expression The variance of the column or expression, ignoring NULL values
9
Group Functions

• Use DISTINCT to suppress duplicate values
• ALL is the default
• All group functions ignore NULL values except
  COUNT()
• Use NVL or NVL2 to replace NULL values

10
SUM Function

• Act on a specific numeric column or expression
• Calculates total amount for a group of rows
• Ignores NULL values
• Syntax
• SUM (column_name numeric _calculated_value)
• Example
• Calculate total profit for a specific order
• SQLgt SELECT SUM(retail-cost) "Total Profit"
• 2 FROM orderitems NATURAL JOIN books
• 3 WHERE order 1007
• Total Profit
• ------------
• 73.48

11
SUM Function

• Example
• Calculate total sales for one day
• SQLgt SELECT SUM(quantity retail) "Total Sales"
• 2 FROM orders
• 3 JOIN orderitemsON orders.order
  orderitems.order
• 4 JOIN booksON orderitems.ISBN books.ISBN
• 5 WHERE orderdate '02-APR-03'
• Total Sales
• -----------
• 387.15

12
AVG Function

• Act on a specific numeric column or expression
• Calculates average of the values in a specified
  column
• Ignores NULL values
• Syntax
• AVG(column_name numeric _calculated_value)
• Example
• Find average profit generated by all books in
  computer category
• SQLgt SELECT AVG ( retail- cost) "Average Profit"
• 2 FROM books
• 3 WHERE category 'COMPUTER'
• Average Profit
• --------------
• 18.2625

13
COUNT Function

• Two main formats
• COUNT()
• Returns number of rows that satisfy the criteria,
  including duplicate rows and those rows with null
  values
• COUNT ( DISTINCTALL expression)
• Return the number of non-null values of rows that
  satisfy the criteria
• ALL is the default
• If DISTINCT is used only unique values are
  counted

14
COUNT () Function

• Column type can be either numeric or non-numeric
• The only function that takes as an argument
• () will count any existing record, even for NULL
  values
• Example
• How many orders have not been shipped yet?
• SQLgt SELECT COUNT()
• 2 FROM orders
• 3 WHERE shipdate IS NULL
• COUNT()
• ----------
• 6

15
COUNT (ALL col) Function

• Display the number of values present in a
  specific column
• Examples

Command Number displayed
SELECT COUNT() FROM CUSTOMERS 20
SELECT COUNT(referred) FROM CUSTOMERS 5
SELECT COUNT(ALL referred) FROM CUSTOMERS 5
SELECT COUNT(DISTINCT referred) FROM CUSTOMERS 3
16
COUNT (DISTINCT) Function

• Include column name in argument to count number
  of occurrences
• Removes duplicates
• Example
• Return the number of book categories
• SQLgt SELECT COUNT(DISTINCT category)
• 2 FROM books
• COUNT(DISTINCTCATEGORY)
• -----------------------
• 8

17
COUNT Function Pitfalls

• Caution-Error in syntax!!
• Example
• This will return the number of rows in books, not
  the categories
• SQLgt SELECT DISTINCT COUNT( category)
• 2 FROM books
• COUNT(CATEGORY)
• ---------------
• 14

18
COUNT Function Pitfalls

• Example
• WHERE clause restricts records to only having a
  NULL value in shipdate column
• Since the specified column had no value, there
  was nothing to count
• SQLgt SELECT COUNT(shipdate)
• 2 FROM orders
• 3 WHERE shipdate IS NULL
• COUNT(SHIPDATE)
• ---------------
• 0
• To count the number of items that were not
  shipped, you need to use COUNT ()
• SQLgt SELECT COUNT()
• 2 FROM orders
• 3 WHERE shipdate IS NULL
• COUNT()
• ----------
• 6

19
MAX MIN Functions

• Returns largest/smallest value in the specified
  column
• Field could be of numeric, character, or date
  type
• MAX/MIN on characters act on the ASCII code
• Ignores NULLs
• Syntax
• MAX(DISTINCTALL Filed_name)
• MIN(DISTINCTALL Filed_name)
• Example
• What is the maximum profit from books
• SQLgt SELECT MAX( retail - cost) "Highest Profit"
• 2 FROM books
• Highest Profit
• --------------
• 41.95

20
MAX MIN Function

• Example
• Which book has the largest (ASCII code) title
• SQLgt SELECT MAX(title)
• 2 FROM Books
• MAX(TITLE)
• ------------------------------
• THE WOK WAY TO COOK
• Example
• SQLgt SELECT MIN (pubdate)
• 2 FROM Books
• MIN (PUBDA
• ---------
• 09-MAY-99

21
GROUP BY Clause

• Used to group data in a table
• Must be used for individual column in the SELECT
  clause with a group function
• Cannot reference column alias
• Syntax
• SELECT column_list
• FROM table_name
• WHERE condition_list
• GROUP BY column_list
• HAVING condition _list
• Note
• WHERE / ORDER BY combination display results
  sorted by a specific column
• GROUP BY / HAVING combination perform similar
  task, but on groups

22
GROUP BY Clause

• Rules for GROUP BY
• If a group function is used in a SELECT clause,
  any individual column listed in the SELECT clause
  must also be listed in the GROUP BY clause
• Columns used in the GROUP BY don't have to be
  used in the SELECT clause
• Column aliases can't be used in the GROUP BY
  clause
• GROUP BY will group the result in ascending order
  of the columns listed in GROUP BY.
• If you want the result to be in a different
  order, use ORDER BY clause.

23
GROUP BY Clause

• Caution!!
• If we try to combine a single column name and a
  group function, we will have an error
• Example
• SQLgt SELECT category, TO_CHAR (AVG (retail -
  cost), '999.99') Profit
• 2 FROM books
• SELECT category, TO_CHAR (AVG (retail - cost),
  '999.99') Profit
• ERROR at line 1
• ORA-00937 not a single-group group function
• To correct the error, use GROUP BY on category
• SELECT category, TO_CHAR (AVG (retail - cost),
  '999.99') Profit
• FROM books
• GROUP BY category

24
GROUP BY Clause

• Example
• SQLgt SELECT category, TO_CHAR (AVG (retail -
  cost), '999.99') Profit
• 2 FROM books
• 3 GROUP BY category
• CATEGORY PROFIT
• ------------ -------
• BUSINESS 16.55
• CHILDREN 12.89
• COMPUTER 18.26
• COOKING 8.60
• FAMILY LIFE 24.88
• FITNESS 12.20
• LITERATURE 18.10
• SELF HELP 12.10
• 8 rows selected.

25
GROUP BY Clause

• Example
• SQLgt SELECT title, MAX( retail - cost) "Highest
  Profit"
• 2 FROM books
• SELECT title, MAX( retail - cost) "Highest
  Profit"
• ERROR at line 1
• ORA-00937 not a single-group group function
• Correction using GROUP BY
• First the books will be ordered by title, then
  the highest profit for that title
• SQLgt SELECT title, MAX( retail - cost) "Highest
  Profit"
• 2 FROM books
• 3 GROUP BY title
• TITLE Highest Profit
• ------------------------------ - -------------
• BIG BEAR AND LITTLE DOVE 3.63
• BODYBUILD IN 10 MINUTES A DAY 12.2
• BUILDING A CAR WITH TOOTHPICKS 22.15
• COOKING WITH MUSHROOMS 7.45

26
GROUP BY Clause

• Example
• List the amount due from each customer for each
  order.
• First use GROUP BY on orders, then use SUM on
  items ordered

SQLgt SELECT customer, order, SUM(quantity
retail) "Order Total" 2 FROM orders NATURAL
JOIN orderitems 3 NATURAL JOIN books 4
GROUP BY customer, order CUSTOMER
ORDER Order Total ---------- ----------
----------- 1001 1003
106.85 1001 1018 75.9
1003 1006 54.5 1003
1016 89.95 1004
1008 39.9 1005 1000
19.95 1005 1009 41.95
1007 1007 347.25 1007
1014 44 1008
1020 19.95 1010 1001
121.9 1010 1011 89.95
1011 1002 111.9 1014
1013 55.95 1015
1017 17.9 1017 1012
170.9 1018 1005 39.95
1018 1019 22 1019
1010 55.95 1020
1004 179.9 1020 1015
19.95 21 rows selected.
27
HAVING Clause

• Serves instead of the WHERE clause for grouped
  data
• Because the WHERE clause can't contain group
  functions, HAVING clause is used to restrict
  groups returned by a query
• WHERE clause specifies which rows will be
  processed
• HAVING clause specifies which group will be
  displayed
• Syntax
• HAVING group_Function comparison_Operator value
• Comparison operators gt, gt, lt, lt, , !
• AND, OR, and NOT can also be used to join group
  conditions

28
HAVING Clause

• Example
• Display groups that have profit gt 15.00
• SQLgt SELECT category, TO_CHAR(AVG( retail -
  cost), '999.99') Profit
• 2 FROM books
• 3 GROUP BY category
• 4 HAVING AVG( retail - cost) gt 15
• CATEGORY PROFIT
• ------------ -------
• BUSINESS 16.55
• COMPUTER 18.26
• FAMILY LIFE 24.88
• LITERATURE 18.10

29
HAVING Clause

• Example
• Display groups that have profit gt 15.00 and
  whose publication date after Jan, 1, 2002
• SQLgt SELECT category, TO_CHAR(AVG( retail -
  cost), '999.99') Profit
• 2 FROM books
• 3 WHERE pubdate gt '01-JAN-02'
• 4 GROUP BY category
• 5 HAVING AVG( retail - cost) gt 15
• CATEGORY PROFIT
• ------------ -------
• CHILDREN 22.15
• COMPUTER 16.60

30
HAVING Clause

• Example
• Display the orders with amount due that is gt 100
• SQLgt SELECT customer, order, SUM(quantity
  retail) "Order Total"
• 2 FROM orders NATURAL JOIN orderitems
• 3 NATURAL JOIN books
• 4 GROUP BY customer, order
• 5 HAVING SUM( quantity retail) gt 100
• CUSTOMER ORDER Order Total
• ---------- ---------- -----------
• 1001 1003 106.85
• 1007 1007 347.25
• 1010 1001 121.9
• 1011 1002 111.9
• 1017 1012 170.9
• 1020 1004 179.9
• 6 rows selected.

31
Order of Clause Evaluation

• When included in the same SELECT statement,
  evaluated in order of
• WHERE
• Gets the rows satisfying the WHERE criteria
• GROUP BY
• Rows are grouped, group function applied to the
  groups
• HAVING
• Groups matching the HAVING criteria are displayed

32
Nesting Group Functions

• Inner function resolved first
• Maximum nesting depth 2
• Single-row functions can be nested inside group
  functions
• Example
• SQLgt SELECT AVG( SUM( quantity retail)) "Average
  Order Total"
• 2 FROM orders NATURAL JOIN orderitems
• 3 NATURAL JOIN books
• 4 GROUP BY order
• Average Order Total
• -------- ----- ------
• 82.2 1190 48
• Notes!!
• SUM is nested inside AVG
• GROUP BY will first group records based on order
• Then total order amount is calculated for each
  order by SUM function
• Then AVG calculates the average of total order
  amounts

33
Statistical Group Functions

• Used to perform calculations for data analysis
• Basic statistical calculations
• Includes
• STDDEV
• VARIANCE
• Standard deviation
• Is Statistical approximation based on normal
  distribution (the root mean square RMS)
• FOLDOC definition
• Wikipedia
• Determine how close values are to the average
  value
• Usually compared to the average
• Help give a quick picture without looking into
  details

34
Statistical Group Functions

• Variance
• The square of the standard deviation
• FOLDOC definition
• Wikipedia
• Normal distribution
• If you input large number of values, they tend to
  cluster around some average value
• Some values may be extreme and they affect the
  group average
• Wikipedia

35
STDDEV Function

• Calculates standard deviation for grouped data
• Syntax
• STDDEV(DISTINCTALL numeric_column)
• Example
• Display the average and standard deviation of the
  profit for books
• SQLgt SELECT category, AVG( retail - cost),
  STDDEV( retail - cost)
• 2 FROM books
• 3 GROUP BY category
• CATEGORY AVG(RETAIL-COST) STDDEV(RETAIL-COST
  )
• ------------ ---------------- -----------------
  --
• BUSINESS 16.55
  0
• CHILDREN 12.89
  13.0956176
• COMPUTER 18.2625
  11.2267074
• COOKING 8.6
  1.6263456
• FAMILY LIFE 24.875
  24.1476966
• FITNESS 12.2
  0
• LITERATURE 18.1
  0

36
VARIANCE Function

• Determines how widely data are spread in a group
• Based on minimum maximum values for a specific
  field
• If a group of data contains only one value, the
  variance will be zero
• Not measured with the same unit as the source
  data
• Syntax
• VARIANCE (DISTICTALL numeric_filed)

37
VARIANCE Function

• Example
• SELECT category, VARIANCE ( retail - cost),
• MIN( retail - cost), MAX( retail - cost)
• FROM books
• GROUP BY category

38
Summary

• AVG, SUM, STDDEV, VARIANCE only for numeric
  fields
• COUNT, MAX, MIN for any data type
• AVG, SUM, MAX, MIN, STDDEV, VARIANCE ignore NULL
  values
• To include NULL values, use NVL
• COUNT () counts records including NULL values
• Use DISTINCT if you dont want to include
  duplicate values
• GROUP BY divide table data into groups
• If SELECT clause contains both individual field
  names group functions, field name must be
  included in the GROUP BY clause
• HAVING restricts groups in group functions
• Group functions can be nested to two levels
• STDDEV, VARIANCE perform statistical analysis

39
Assignment

• In-Class group discussion
• Answer all review questions, p. 181
• Answer all multiple-choice questions, pp. 181-186
• Hands-On Assignments, p. 186
• Homework assignment
• Read Oracle Academy Database Programming
  Section 5, Lessons 4 5 and Section 6 Lessons 1
• A Case for Oracle9i, p. 187
• Due Date See announcements page