Lecture4.pdf

GROUP BY / HAVING + Aggregation Operators

Syntax:

• GROUP BY clause
  • list of(expressions involving) columns used for grouping
• HAVING clause
  • group qualification conditions
• aggregation operators: COUNT, AVG, SUM, MIN, MAX
• group
  • a collection of rows with identical values for the columns in grouping-list
  • every row in the result of the query corresponds to a group
• select-list
  • columns (that must appear in grouping-list)
  • terms of the form aggop(column) [AS NewName]

    • e.g.

      MAX(R.ImpactFactor) AS MaxImpactFactor

      • NewName assigns a name to the column in the result table
• group-qualification
  • expressions with a single value / group
  • a column in group-qualification appears in grouping-list or as an argument to an aggregation operator
• records that meet qualification are partitioned into groups based on the values of the columns in grouping-list
• an answer row is generated for every group that meets group-qualification

SELECT [DISTINCT] select-list
FROM from-list
WHERE qualification
GROUP BY grouping-list
HAVING group-qualification

Examples:

1. Find the age of the youngest researcher for each impact factor.

• discussion: using the GROUP BY clause vs writing n queries, one for each of the n values of the impact factor, where n depends on the relation instance

SELECT R.ImpactFactor, MIN(R.Age)
FROM Researchers R
GROUP BY R.ImpactFactor

1. Find the age of the youngest researcher who is at least 18 years old for each impact factor with at least 10 such researchers.

SELECT R.ImpactFactor, MIN(R.Age) AS MinAge
FROM Researchers R
WHERE R.Age >= 18
GROUP BY R.ImpactFactor
HAVING COUNT(*) >= 10

1. Find the average age of researchers who are at least 18 years old for each impact factor with at least 10 researchers.