Aggregate Functions

What does HAVING do?

Limit the results of a query based on an aggregate property.

What do Aggregate Functions do?

Combine multiple rows together to form a single value of more meaningful information.

What do Aggregate functions do?

Compute a single result set from a set of values.

True or False The WHERE clause filters rows, whereas the HAVING clause filter groups.

True. The HAVING clause was added to SQL because WHERE could not be used with aggregate functions.

What does AVG() do?

Averages of the values in a column.

What does GROUP BY do?

Is a clause used with aggregate functions to combine data from one or more columns.

What does the following query do? SELECT neighborhood, AVG(price) FROM apartments GROUP BY neighborhood;

It calculates the average price of apartments in each neighborhood.

What does the following query do? SELECT price, COUNT(*) FROM menu WHERE orders > 50 GROUP BY price;

It calculates the total number of menu items that have been ordered more then 50 times - for each price.

What does COUNT() do?

It counts the number of rows.

What does the following query do? SELECT genre, SUM(downloads) FROM kindle GROUP BY genre;

It returns the total amount of downloads - for each genre.

Find the error in this code: SELECT COUNT(*) FROM songs HAVING plays > 100;

It should be WHERE instead of HAVING.

What do MAX()/MIN() do?

Pick the largest or smallest value.

What does ROUND() do?

Round the values in the column.

How would you calculate the minimum number of stops from the train table?

SELECT MIN(stops) FROM train;

Which function takes a column and returns the total sum of the numeric values in that column?

SUM()

What does the ROUND function take as argument(s)?

The column name, and the number of decimal places to round the values in the column to.

What does the COUNT() function take as argument(s)?

The name of a row or *.

What does SUM() do?

The sum of the values in a column.