Lesson 5: Relational Operations

Introduction

Result sets of aggregated data can be generated. With these additional relational operations a single result tuple can represent the aggregate result of an operation applied to one, many, or all of the tuples in a table. Further, these operations can be applied to a series of subsets of a result set, i.e. tuples can be grouped together based upon some selection condition. These operations include:

By default each of these aggregate operations is applied to all tuples in a result set. To generate subsets, or groups of results, SQL provides the GROUP BY operator.

SQL: SELECT ... [WHERE ...] GROUP BY attribute

The GROUP BY operator cannot be used on its own. It must be used with one of the additional relational operators.

COUNT

Returns the count of the number of tuples fitting a given condition. NULL values, if they exist, are counted. The other aggregate functions ignore NULL values.

SQL
SELECT COUNT(attribute) FROM table
Examples
Count All Rows in Table
              
SELECT COUNT(*) AS memberCount FROM member

Returns the number of members in the member table and names the resulting attribute memberCount. The wildcard attribute * simply counts all rows.

Count Distinct Surnames
              
SELECT COUNT(DISTINCT memberSurname) AS memberCount FROM member
  WHERE YEAR(memberBirthDate) > 1950

Returns the number of members born after 1950, and counts only the number of unique surnames.

Count Expertises
              
SELECT COUNT(*) AS expertiseCount FROM memberBroad

Returns the number of total number of different expertises held by all members in the DCRIS database.

Count Expertises Per Member
              
SELECT memberBroadMemberId, COUNT(*) AS expertiseCount FROM memberBroad
  GROUP BY memberBroadMemberId

Returns the number of expertises held by each individual member of the DCRIS database. The GROUP BY clause causes the counts to be grouped together by individual member IDs. The GROUP BY clause allows the attribute being grouped by to appear in the final result set.

Count Members Per Expertise
              
SELECT memberBroadBroadId, COUNT(*) AS memberCount FROM memberBroad
  GROUP BY memberBroadBroadId

Returns the number of members for each expertise in the DCRIS database. Again, the attribute resulting from the count is given a name.

SUM

Returns the sum of the values in a numeric attribute.

SQL
SELECT SUM(attribute) FROM table
Examples
Sum all Sales
              
SELECT SUM(Sales * Price) AS totalSales
  FROM pub

Returns the total sales of all publications with the alias totalSales.

Sum Sales by Member
              
SELECT pubMemberId, SUM(Sales * Price) AS totalSales
  FROM pub
  GROUP BY pubMemberId

Returns the total sales of all publications with the alias totalSales for each individual member.

AVG

Returns the average value of a given attribute.

SQL
SELECT AVG(attribute) FROM table
Examples
Average Sales
              
SELECT AVG(Sales) AS averageSales FROM pub

Returns the average number of copies sold for each publication.

MAX/MIN

Returns the maximum and minimum values respectively of a given attribute.

SQL
SELECT MIN(attribute) FROM table
SELECT MAX(attribute) FROM table
Examples
Maximum Sales
              
SELECT MAX(Sales * Price) AS maxSales
  FROM pub
  WHERE pubPubType = 'B'

Returns the maximum sales from all publications that are books.

These operations may be used in combination.

Examples
Various Sales Figures
              
SELECT pubMemberId, COUNT(*) AS members,
  SUM(sales) AS totalSales, AVG(sales) AS averageSales,
  MIN(sales) AS minSales, MAX(sales) AS maxSales
  FROM pub
  GROUP BY pubMemberId

This lists the number, total, average, minimum and maximum of publications sold grouped by individual members.