CP363 : Relational Operations

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:

SELECT COUNT(*) AS memberCount FROM member

Returns the number of members in the member table and names the resulting attribute memberCount.

SELECT COUNT( DISTINCT Last_Name ) FROM member
  WHERE YEAR( Birth_Date ) > 1950

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

SELECT COUNT(*) FROM member_keyword

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

SELECT member_id, COUNT(*) FROM member_keyword
  GROUP BY member_id

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. Note that member_id itself may be part of the result set. The GROUP BY clause allows the attribute being grouped by to appear in the final result set.

SELECT keyword_ID, COUNT(*) AS members FROM member_keyword
  GROUP BY keyword_ID

Returns the number of members who have each individual 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:

SELECT SUM( Sales * Price ) AS Total_Sales
  FROM pub

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

SELECT member_id, SUM( Sales * Price ) AS Total_Sales
  FROM pub
  GROUP BY member_id

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

SELECT member_id, SUM( Sales * Price ) AS Total_Sales
  FROM pub
  WHERE Institution = 'Waterloo'
  GROUP BY member_id

Returns the total sales of all publications for each individual member who works at UW.


AVG()

Returns the average value of a given attribute.

SQL: SELECT AVG( attribute ) FROM table

Examples:

SELECT Avg( Sales ) AS Average_Sales FROM pub

Returns the average number of copies sold for each publication.


MAX() and MIN()

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

SQL: SELECT MIN/MAX( attribute ) FROM table

Examples:

SELECT MAX( Sales * Price ) AS Max_Sales
  FROM pub
  WHERE Title LIKE 'Nuclear %'

Returns the maximum sales from all publications whose titles start with the word 'Nuclear'.


These operations may be used in combination.

Examples:

SELECT member_id, COUNT(*) AS members,
  SUM( Sales ) AS TotalSales, AVG( Sales ) AS AverageSales,
  MIN( Sales ) AS MinSales, MAX( Sales ) AS MaxSales
  FROM pub
  GROUP BY member_id

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