Aggregate Functions

SQL provides aggregate functions to help with the summarisation of large volumes of data. The functions can produce a single value for an entire group or table. The functions operate on sets of rows and return results based on groups of rows.

Examples

Return the number of rows in the book table:

SELECT COUNT(*) 
FROM book;

Return the number of student records:

SELECT COUNT(*) 
FROM student;

Return the number of bookcopies where the ISBN is equal to 123675432:

SELECT COUNT(copyId) as 'JavaScript Book Count' 
FROM bookcopy 
WHERE isbn = '123675432';

Exercises

1. Return the number of students from Waterford. Label the returned value appropriately.

   

2. Return the number of books that contain the term Database in the title. Label the returned value appropriately.

   

3. Return the maximum book price. Label the returned value appropriately.

   

4. Return the minimum book price. Label the returned value appropriately.

   

5. Return the average book price. Label the returned value appropriately.

   

6. Return the number of different counties that students come from.

   

Rounding

To round a numeric value to a whole number (or to 2 decimal places for example), use round.

Examples

Round 34.6792 to a whole number (35).

round(34.6792);

Round 34.6792 to 2 decimal places (34.68).

round(34.6792,2);

Exercise

1. Return the average book price rounded to 2 decimal places. Label the returned value appropriately.

   

GROUP BY

The GROUP BY clause groups a result into subsets that have matching values for one or more columns. The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns.

Example

The following example returns the number of students grouped by (per) county.

SELECT county, COUNT(*) 
FROM student 
GROUP BY county;

This will return one computed value for each different value of county. In the select statement, the attribute used in the GROUP BY clause must be one of the attributes returned.

Exercises

1. Return the number of bookcopies. Label the returned value appropriately.

   

2. Count the number of bookcopies by ISBN. Rename the count column as Number of Copies of each Book.

3. Count the number of books per book category. Again, rename the count column.

   

4. Return the minimum and maximum priced book for each publisher. Again, rename the calculated columns.

   

HAVING

In order to restrict rows returned with aggregate functions, you must use a HAVING clause. Essentially it is a WHERE clause for aggregate functions.

SELECT county, COUNT(*) 
FROM student 
GROUP BY county 
HAVING COUNT(*) >= 5;

Exercises

1. Using the solution to Number 3 (on the previous page); return records for categories that only have 3 or more books.

   

2. Using the solution to Number 4 (on the previous page); return records where the maximum priced book is >=60.00.

   

Exercise

• Download and open the Movies script (as an SQL file).
• The Movies database has 3 tables: film, reviewer, and rating.
• Open the SQL script in MySQL Workbench.
• Open a new tab and save it as Review Exercise. Enter (and execute) the following commands:

USE MOVIES; /*Select the Movies database*/

SHOW TABLES; /*List all the tables*/

To do:

1. Describe each table.

2. Find the titles of all films directed by Steven Spielberg.

3. Return the title and year of all films produced from 1940 to 1990. Sort by title.

4. Return the title and length of all films whose length is 110 minutes or more. Sort by title.

5. Return the title and director of all films where the director's surname begins with C.

6. Return the number of stars, film Id (output as Film Id), and ratingdate of all movie ratings where the rating given is 4 or 5. Sort the output in descending order of the stars and then in ascending order of movie id.

7. Some reviewers didn't provide a date with their rating. Find the rID of all reviewers who have ratings with a NULL value for the date.

8. Return the number of films. Label the returned column Number of Films.

9. Return the number of reviewers. Label the returned column Number of Reviewers.

10. Return the number of ratings. Label the returned column Number of Ratings Provided.

11. Return the number of films per director. Label the returned column for the calculation - Number of Films.

12. Return the number of ratings left by each reviewer. Label the returned column for the calculation - Number of Reviews (per Reviewer).

13. Return the average number of film minutes per director. Label the returned column for the calculation - Average Number of Minutes per Film. The returned value is to be output as a whole number (no decimal places).

Solutions

Solutions for the exercises in this lab are available here: Week2.zip