03192003

1
Using Summary Queries in DB2

• Pam Odden

2
Objectives

• What is a summary query?
• Column functions
• Duplicate row elimination (DISTINCT)
• Grouping rows by particular columns (GROUP BY)
• Group search conditions (HAVING)

3
What is a Summary Query?

• Many requests for information dont require the
  level of detail provided by individual database
  rows.
• Each of the following examples asks for a single
  value or a small number of values that summarize
  information in the database
• What is the average kindergarten class size?
• What is the latest date a student took a
  proficiency test?
• What is the total number of sick days used by a
  department in a pay period?
• SQL supports requests for summary data through
  column functions and the GROUP BY and HAVING
  clauses

4
Column Functions

• A column function takes an entire column of data
  as its input and produces a single data item that
  summarizes the column.
• Column functions are SUM( ), AVG( ), MIN( ), MAX(
  ), and COUNT()
• The argument to a column function can be a simple
  column name, or an SQL expression.
• Select the average number of credits earned in a
  grade and school
• SELECT AVG(CREDEARNED)
• FROM SSASIDB1.ASTU_STUDENT
• WHERE SCHOOLNUM '351'
• AND GRADE '12'
• --------------------------
• 20.12910246679
• Select the average percentage of credits
  attempted that are actually earned
• SELECT AVG(100 (CREDEARNED/CREDATMPT))
• FROM SSASIDB1.ASTU_STUDENT
• WHERE SCHOOLNUM '351'
• AND GRADE '12'
• AND CREDATMPT 0
• -------------------------------------

5
SUM( ), AVG( ) and COUNT()

• For SUM and AVG, the data in the column must have
  a numeric type
• The result of SUM( ) has the same basic data type
  as the data in the column, although the result
  may have a higher precision
• The result of AVG( ) may be decimal, even if the
  column is integer or smallint
• COUNT() returns the number of rows qualifying
  for the selection criteria. It always returns an
  integer. If there are no qualifying rows, it
  returns zero.
• SELECT COUNT()
• FROM SSASIDB1.ASTU_STUDENT
• WHERE SCHOOLNUM '351'
• AND GRADE '12'
• -------------------------
• 527

6
MIN( ) and MAX( )

• MIN( ) and MAX( ) find the smallest and largest
  values in a column
• The column may be numeric, string, or date/time
  data types
• The result has the same data type as the column
• Numbers are compared in algebraic order
• Dates are compared sequentially
• String comparisons depend on the character set
  being used
• SELECT MAX(CREDEARNED)
• FROM SSASIDB1.ASTU_STUDENT
• WHERE SCHOOLNUM '351'
• AND GRADE '12'
• --------------------------
• 32.500

7
Programming Considerations for Column Functions

• Be careful not to mix column functions with
  regular column names in a single select
  statement, when not using GROUP BY
• Column functions may not be nested
• If any of the values in a column are null, they
  are ignored when the column functions value is
  computed
• If every data item in the column is null, or if
  no rows are selected, then SUM( ), AVG( ), MIN(
  ), and MAX( ) return null.
• COUNT() counts rows and does not depend on
  whether columns are null. If there are no rows,
  it returns zero.

8
Duplicate Row Elimination (DISTINCT)

• DISTINCT can be used to cause SQL to eliminate
  duplicate values from a column before applying a
  column function to it
• The word DISTINCT is included in the parentheses
  before the column argument.
• The DISTINCT keyword may be specified only once
  in a query. If it appears in the argument of one
  column function, it cant appear in any others.
  If it is specified before the select list, it
  cant appear in an column functions.
• Count total rows in ASTU_STUDENT
• SELECT COUNT()
• FROM SSASIDB1.ASTU_STUDENT
• ------------------------------
• 423627
• Count total individual students in ASTU_STUDENT
• SELECT COUNT(DISTINCT PERMNUM)
• FROM SSASIDB1.ASTU_STUDENT
• ------------------------------
• 420742
• Calculate average credit earned without including
  duplicate values
• SELECT AVG(DISTINCT CREDEARNED)
• FROM SSASIDB1.ASTU_STUDENT

9
Grouped Queries (GROUP BY)

• Column functions alone provide summary
  information condensed into a single summary row,
  like a total line at the bottom of a report.
  Sometimes we want subtotals as well. Grouping
  provides this function.
• Count students in each school
• SELECT SCHOOLNUM, COUNT()
• FROM SSASIDB1.ASTU_STUDENT
• GROUP BY SCHOOLNUM
• SCHOOLNUM
• ----------------------------
• 201 1133
• 202 1141
• 203 824
• 204 830
• Show average number of credits attempted and
  earned in each grade
• SELECT GRADE, AVG(CREDEARNED), AVG(CREDATMPT)
• FROM SSASIDB1.ASTU_STUDENT
• WHERE SCHOOLNUM '351'
• GROUP BY GRADE

10
Programming Considerations for Grouped Queries

• The grouping columns must be actual columns of
  the table named in the FROM clause. Queries may
  not be grouped on the value of expressions.
• All of the items in the SELECT list must have
  only one value for each group of rows. This
  means the selected item must be either
• A constant
• A column function
• A grouping column
• An expression using combinations of the above
• SQL ignores information about primary and foreign
  keys and unique indexes when analyzing the
  validity of a grouped query. Even though there
  is a unique index on soc_sec_num, below, we are
  forced to use the redundant grouping column
  LNAME.
• SELECT A.SOC_SEC_NUM, LNAME, SUM(CUR_AMT)
  
• FROM MSPAYDB1.MSPAY_DEDUCTIONS A INNER JOIN
  MSPAYDB1.MSPAY_ID B
• ON A.SOC_SEC_NUM B.SOC_SEC_NUM
  
• WHERE A.SOC_SEC_NUM '101409194'
  
• GROUP BY A.SOC_SEC_NUM, LNAME
  
• SOC_SEC_NUM LNAME
  
• ---------------------------------------------
  ----------------
• 123456789 SMITH
  10.00

11
Group Search Conditions (HAVING)

• The HAVING clause is used to include or exclude
  row groups from the query results, so the search
  condition is applied to the entire group, not
  each individual member
• Column(s) in HAVING clause must apply to whole
  group, similar to SELECT list on prior page
• The syntax of the SELECT statement does not
  require that HAVING only be used with grouped
  queries. If there is no grouping the HAVING
  clause is applied to the all the rows together as
  one group.
• Select groups of school buses with total
  acquisition cost over 1M
• SELECT A.COMMODITY_ID, SUBSTR(A.COMMODITY_NAME,1,2
  5),
• SUM(B.EL_ACQ_COST), COUNT()
  
• FROM M7025DB2.EQP_COM A INNER JOIN
  M7025DB2.EQP_LIST B
• ON A.COMMODITY_ID B.EL_COMMODITY_ID
  
• WHERE A.COMMODITY_NAME LIKE 'BUS'
  
• GROUP BY A.COMMODITY_ID, A.COMMODITY_NAME
  
• HAVING SUM(B.EL_ACQ_COST) 1000000.00
  
• ORDER BY 3 DESC
  
• COMMODITY_ID
  
• ---------------------------------------------
  ---------------------
• 07000505300 VEHICLE, BUS, 48-54 PASSE
  53910014.91 990
• 07000505800 VEHICLE, BUS, OVER 85 PAS
  45574105.60 467
• 07000505400 VEHICLE, BUS, 60-67 PASSE
  6625281.16 110

12
Using HAVING to Find Duplicate Rows

• A handy use of the HAVING clause is to find
  duplicate rows in a table where you dont expect
  duplicates, although they are not ruled out by a
  unique index or a primary key.
• Group by the column or columns that you are
  interested in, and use HAVING to return only the
  groups with count more than 1.
• Find students with more than one row on the
  ASTU_STUDENT table
• SELECT SUBSTR(FIRSTNAME,1,20) AS FIRSTNAME,
  
• SUBSTR(LASTNAME,1,20) AS LASTNAME,
  
• CCSD_ID, COUNT()
  
• FROM SSASIDB1.ASTU_STUDENT
  
• GROUP BY CCSD_ID, FIRSTNAME, LASTNAME
  
• HAVING COUNT() 1
  
• ---------------------------------------------
  --------------
• FIRSTNAME LASTNAME
  CCSD_ID
• ---------------------------------------------
  --------------
• A'PRIAL BASPED
  000466 2
• NICOLE ADAMS
  000468 2
• ANA BERNAL
  000474 2
• MOSES ROBLEDO
  000512 2

13
Summary

• Summary queries use SQL column functions to
  collapse a column of data into a single value.
• Column functions can compute the average, sum,
  minimum, and maximum values of a column or count
  the number of rows of query results.
• A summary query without a GROUP BY clause
  generates a single row of query results,
  summarizing all the qualifying rows.
• A summary query with a GROUP BY clause generates
  multiple rows of query results, each summarizing
  the rows in a particular group.
• The HAVING clause acts as a WHERE clause for
  groups