Aggregates

Question 1

INTRO

Answer 1

Aggregates are calculations performed on multiple rows.

Types:

COUNT( ) : count the number of rows
SUM( ) : sum of the values in a column
MAX( ) / MIN( ) : largest / smallest value
AVG( ) : average of the values in a column
ROUND( ) : round the values in a column

Question 2

COUNT

Answer 2

SELECT COUNT (*)
FROM table_name;

Passing * as an argument will count every row.

Question 3

SUM

Answer 3

SELECT SUM(column_name)
FROM table_name;

Will return the total number from the specified column.

Question 4

MAX / MIN

Answer 4

SELECT MAX(column_name)
FROM table_name;

SELECT MIN(column_name)
FROM table_name;

Question 5

AVERAGE

Answer 5

SELECT AVG(column_name)
FROM table_name;

Question 6

ROUND

Answer 6

Takes two arguments inside the parenthesis: a column name, an integer. The integer represents the number of decimal places to be rounded to.

SELECT ROUND(column_name, integer)
FROM table_name;

Can also separate out…

SELECT column_name, ROUND(column_name, integer)
FROM table_name;

Or if you wanted to round an average to two decimals…

SELECT ROUND(AVG(column_name), 2)
FROM table_name;

Question 7

GROUP BY

Answer 7

Used with the SELECT statement to arrange identical data into groups.

Example:

SELECT year,
    AVG(imdb_rating)
FROM movies 
GROUP BY year
ORDER BY year;

Or…

SELECT price, COUNT(*)
FROM fake_apps
GROUP BY price;

We can also use GROUP BY to calculate in a specific column. 1 is used for the first column, 2 is used for the second, and so forth.

Example:

SELECT category, price,
AVG(downloads)
FROM fake_apps
GROUP BY 1, 2;

Question 8

HAVING

Answer 8

Similar to WHERE, but instead, HAVING will filter groups rather than rows.

The HAVING statement always comes after GROUP BY but before ORDER BY and LIMIT.

SELECT year, genre, COUNT(name)
FROM movies
GROUP BY 1, 2
HAVING COUNT(name) > 10;

Or…

SELECT price
    ROUND(AVG(downloads)),
    COUNT(*)
FROM fake_apps 
GROUP BY price;

Will return the average downloads (rounded) and the number of apps at each price point. But when you add HAVING…

SELECT price, 
    ROUND(AVG(downloads)),
    COUNT(*)
FROM fake_apps 
GROUP BY price
HAVING COUNT(*) > 10;

Will restrict the query to price points that have more than 10 apps.