Aggregate Functions Flashcards
What are Aggregates?
Calculations performed on multiple rows of a Table.
Which function is the fastest way to calculate how many rows are in a table?
COUNT( )
What does this statement do?
SELECT COUNT(*)
FROM _______;
Counts every row
Which function makes it easy to add all values in a particular column?
SUM( )
What does this statement do?
SELECT SUM(downloads)
FROM ______;
This adds all values in the downloads column
What does the SUM( ) function do?
It takes the name of a column as an argument and returns the sum of all the values in that column.
Where are arguments passed in the functions?
Inside the parentheses of the function.
Which function returns the highest values in a column?
MAX( )
Which function returns the lowest values in a column?
MIN( )
What do all functions have in common?
They take the name of a column as an argument and return the value for the specified information in that column.
Which function quickly calculates the average value of a particular column?
AVG( )
What does this statement do?
SELECT AVG(downloads)
FROM fake_apps;
This statement returns the average number of downloads for an app in the database.
Which function makes the result Table easier to read?
ROUND( )
How many arguments does the ROUND( ) function take?
Two
What are the two arguments that the ROUND( ) function takes inside the parentheses?
A column name
An integer
What does the ROUND( ) function do?
It rounds the values in the column to the number of decimal places specified by the integer.
What does this statement do?
SELECT ROUND(price, 0)
FROM fake_apps;
This passes the column price and integer 0 as arguments. SQL rounds the values in the column to 0 decimal places in the output.
What clause is used with aggregate functions and with the SELECT statement to arrange identical data into groups?
GROUP BY
Where does the GROUP BY statemenet go in a query?
It goes after any WHERE statements, but before ORDER BY or LIMIT.
What does this statement do?
SELECT year,
AVG(imdb_rating)
FROM movies
GROUP BY year
ORDER BY year;
SELECT year column to get the average imdb_rating of particular years FROM the movies Table
Unlike GROUP BY I, GROUP BY II uses ?
Column references
What are the column references in GROUP BY?
1 is the first column selected
2 is the second column selected
3 is the third column selected
What does this GROUP BY II do?
SELECT ROUND(imdb_rating)
COUNT(name)
FROM movies
GROUP BY 1
ORDER BY 1;
The 1 refers to the first column in our SELECT statement, ROUND(imdb_rating)
Why would GROUP BY II be used?
To GROUP BY a calculation done on a column.
What clause allows the filtering of groups?
HAVING
True or False: HAVING allows you to filter which groups to include and which to exclude?
True
What is the difference between WHERE and HAVING?
WHERE filters the rows while HAVING filters the groups.
What types of WHERE clauses can be used with HAVING?
ALL of them
What does this statement do?
SELECT year,
genre,
COUNT(name)
FROM movies
GROUP BY 1, 2
HAVING COUNT(name) > 10;
This selects the year and genre column while counting the name column from the movies Table and grouping them by column 1 and column 2. The groups will then be filtered by the HAVING that counts the name column and sets a condition to see if its greater than 10.
What clause is used to limit the results of a query based on values of the individual rows?
WHERE
What clause is used to limit the results of a query based on an aggregate property?
HAVING
Where does a HAVING statement go in a query?
Always after GROUP BY but before ORDER BY and LIMIT.
What are aggregate functions?
Combine multiple rows together to form a single value of more meaningful information.
