Section 9 - The Magic of Aggregate Functions

Question 1

Give a few examples of aggregate data.

Answer 1

• Finding averages
• Sum a bunch of different things
• Grouping things by author, title, genre, sales numbers, class size, etc
• Average page count per author, etc.

Question 2

What does the COUNT function do?

Answer 2

It does pretty much what you’d expect it to do (it COUNTs things) - at least in its basic form.

Question 3

What would the following function do?

SELECT COUNT(*) from sports_cars;

Answer 3

This would COUNT all the sports_cars in our database.

Question 4

What function counts things?

Answer 4

The COUNT function.

Question 5

What would the following function do?

SELECT COUNT(repair_guides) FROM help_you_fix;

Answer 5

This would COUNT all repair_guides (column) from the help_you_fix database.

Question 6

What might be incorrect about the following function?

SELECT COUNT(car_manufacturer) FROM cars;

Answer 6

This would count ALL instances of car_manufacturers FROM our cars database.

BUT, what if you had 20, 30, 40+ models from each manufacturer? This COUNT function would could each instance separately, so if you had, say, 20 different Toyota models, it would count Toyota 20 different times.

With this in mind, use the DISTINCT function, as in the following:

SELECT COUNT(DISTINCT car_manufacturer) FROM cars;

Question 7

Write a function that does the following:

• Finds the number of unique car_models in a database
• The database is named all_things_cars
• Print the total number of unique car models as “This is the total number of cars in the All Things Car database: ____ (

Answer 7

SELECT CONCAT(‘This is the total number of cars in the All Things Car database:’, ‘ ‘, COUNT(DISTINCT car_models)) AS ‘Here’s the Result of Our Cars Query’ FROM all_things_car;

Question 8

What does GROUP BY do?

Answer 8

It summarizes (or “aggregates”) identical data into single rows.

Question 9

What are a few examples of functions you could run with GROUP BY?

Answer 9

• GROUP BY authors who released a book in 2017
• GROUP BY classes whose sizes are 30 students
• Group all of our books by genre
• Group all of our tea bags by tea type and then sort them by sales numbers

Question 10

What do the MIN and MAX functions do?

Answer 10

They help identify minimum and maximum values in a table.

Question 11

Write a function that finds the oldest (lowest) released_year from a table called mazda_cars.

Answer 11

SELECT MIN(released_year) FROM mazda_cars;

Question 12

Write a function that finds the longest book (book_pages = column name) from a table named borders. Print back both the title and the page count. Use the MAX function to do so.

Answer 12

Will the below result give you the longest book title?

SELECT MAX(title) AS ‘This will take you awhile to read’ FROM borders;

NO, it will NOT! It will only give you the last title if the titles were in alphabetical order.

One solution is to run a subquery. You can find this below. (This is the long solution, but you’ll learn the more concise solution later. )

SELECT title, book_pages FROM borders WHERE book_pages = (SELECT MAX(book_pages) FROM borders);

Note that in the above query, you need the FROM statement twice.

Another way to find the same result would be as follows:

SELECT title, book_pages FROM borders ORDER BY book_pages DESC LIMIT 1;

Question 13

Write the following query:

• Find the most recently released car_model from each car_make in the the cars table.
• Print only the most recently released car_model from each car manufacturer/car make. Print car_make, car_model, and car_model_released_year, in that order.

Answer 13

SELECT
car_make, car_model,
MIN(released_year) FROM cars
GROUP BY car_make, car_model;

I think the above is accurate.

Question 14

Give some examples of things you could do with the SUM function.

Answer 14

• SUM the pages all the books in the database.
• SUM the sales for all yellow yoyos within the database
• SUM the sales totals for all orders under $100 places between January and February 2019.
• SUM together the payroll of all operations personal for a month.

Question 15

Write a query that sums together all the pages in a book_shop database.

Answer 15

SELECT SUM(pages) FROM book_shop;

Question 16

Write a query that does the following:

• Columns: author_firstname, author_lastname, pages
• SUM the total number of pages each author has written.
• Print the author’s first name, last name, and the total pages.

Answer 16

SELECT author_firstname, author_lastname, SUM(pages) FROM books GROUP BY author_lastname, author_firstname;

Question 17

What is the function for adding things together?

Answer 17

The SUM function

Question 18

What does the SUM function do?

Answer 18

It adds things together.

Question 19

What does the AVG function do?

Answer 19

It averages things together.

Question 20

What function averages things together?

Answer 20

The AVG function.

Question 21

Write a function that calculates the average released_year across our entire book_shop table.

Answer 21

SELECT AVG(released_year) FROM book_shop;

Question 22

Write a function that does the following:
Finds and prints the average released_year from each of the authors in a table named book_shop.
The function should print out the author_firstname, the author_lastname, and the released_year.

Answer 22

SELECT author_firstname, author_lastname, AVG(released_year) FROM book_shop GROUP BY author_lastname, author_firstname;

Question 23

Write the following function:

• Print the total number of books released each year from a book_shop table.
• columns are released_year, title

Answer 23

SELECT released_year, COUNT(*) FROM book_shop GROUP BY released_year;

You could also do:

SELECT released_year, COUNT(title) FROM book_shop GROUP BY released_year;

OR perhaps:

SELECT released_year, COUNT(released_year) FROM book_shop GROUP BY released_year;

Question 24

Write the following function:

1) Print the total number of books in stock from a book_shop table.
2) Columns are title, stock_quantity, author_firstname, author_lastname, released_date.

Answer 24

Will the following function work?

SELECT COUNT(stock_quantity) FROM book_shop;

NO NO NO! :)
This would just add up the stock_quantity field appears in the table. (ie how many rows the stock_quantity field appears in).

You would need to use the SUM function.

SELECT SUM(stock_quantity) FROM book_shop;

Question 25

Write the following function:

• You have a table named book_shop
• Columns include author_firstname, author_lastname, released_year, title, pages, stock_quantity
• Print the author’s first name, then last name, and the average released year for each of those author’s books

Answer 25

SELECT author_firstname, author_lastname, AVG(released_year) FROM books GROUP BY author_lastname, author_firstname;

Question 26

Write the following function. Write it 2 different ways:

• You have a table named book_shop
• Columns include author_firstname, author_lastname, released_year, title, pages, stock_quantity
• Print the full name of the author who wrote the longest book.

Answer 26

SELECT CONCAT(author_fname, ‘ ‘, author_lname) FROM books ORDER BY pages DESC LIMIT 1;

OR, use the MAX function:

SELECT CONCAT(author_firstname, ‘ ‘, author_lastname) FROM book_shop WHERE pages = (SELECT MAX(pages) FROM book_shop);