Grouping the results of aggregate functions

When using a field and aggregate function together in the same SELECT query you can group the results and have the aggregate performed on the group rather than the whole data set. We will need to add a GROUP BY clause to the end of the SELECT statement defining which field(s) the aggregate should be performed on. Below is the syntax for using GROUP BY.

SELECT field_name, aggregate_function(field_name)
FROM table_name
GROUP BY field_name;

So lets say you wanted to the average price of book of each author. We can use the AVG() function along with the author_id field. Below is an example of that query.

SELECT author_id, AVG(price)
FROM books
GROUP BY author_id;

Lesson Activities

  1. Select the book table and count the total number of books.
    Click to see code
    SELECT COUNT(book_id)
    FROM books;
  2. Select the book table and count the total number of books by author.
    Click to see code
    SELECT author, COUNT(book_id)
    FROM books
    GROUP BY author;
  3. Select the book table and count the total number of books by price.
    Click to see code
    SELECT price, COUNT(book_id)
    FROM books
    GROUP BY price;
  4. Find the average book price by author
    Click to see code
    SELECT author, AVG(price)
    FROM books
    GROUP BY author;

Questions