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.
