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.
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.
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.
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.
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.