Cheatsheets / Learn SQL
Aggregate Functions
Column References
The GROUP BY and ORDER BY clauses can
reference the selected columns by number in SELECT COUNT(*) AS
which they appear in the SELECT statement. 'total_movies',
The example query will count the number of rating
movies per rating, and will:
FROM movies
● GROUP BY column 2 ( rating )
GROUP BY 2
ORDER BY 1;
● ORDER BY column 1 ( total_movies )
MAX() Aggregate Function
The MAX() aggregate function takes the name
of a column as an argument and returns the SELECT MAX(amount)
largest value in a column. The given query will FROM transactions;
return the largest value from the amount
column.
SUM() Aggregate Function
The SUM() aggregate function takes the name
of a column as an argument and returns the SELECT SUM(salary)
sum of all the value in that column. FROM salary_disbursement;
COUNT() Aggregate Function
The COUNT() aggregate function returns the
total number of rows that match the speci ed SELECT COUNT(*)
criteria. For instance, to nd the total number FROM employees
of employees who have less than 5 years of WHERE experience < 5;
experience, the given query can be used.
Note: A column name of the table can also be
used instead of * . Unlike COUNT(*) , this
variation COUNT(column) will not count NULL
values in that column.
GROUP BY Clause
The GROUP BY clause will group records in a
result set by identical values in one or more SELECT rating,
columns. It is often used in combination with COUNT(*)
aggregate functions to query information of FROM movies
similar records. The GROUP BY clause can
GROUP BY rating;
come after FROM or WHERE but must come
before any ORDER BY or LIMIT clause.
The given query will count the number of
movies per rating.
MIN() Aggregate Function
The MIN() aggregate function returns the
smallest value in a column. For instance, to nd SELECT MIN(amount)
the smallest value of the amount column from FROM transactions;
the table named transactions , the given
query can be used.
AVG() Aggregate Function
The AVG() aggregate function returns the
average value in a column. For instance, to nd SELECT AVG(salary)
the average salary for the employees who FROM employees
have less than 5 years of experience, the given WHERE experience < 5;
query can be used.
HAVING Clause
The HAVING clause is used to further lter the
result set groups provided by the GROUP BY SELECT year,
clause. HAVING is often used with aggregate COUNT(*)
functions to lter the result set groups based on FROM movies
an aggregate property. The given query will
GROUP BY year
select only the records (rows) from only years
HAVING COUNT(*) > 5;
where more than 5 movies were released per
year.
ROUND() Function
The ROUND() function will round a number
value to a speci ed number of places. It takes SELECT year,
two arguments: a number, and a number of ROUND(AVG(rating), 2)
decimal places. It can be combined with other FROM movies
aggregate functions, as shown in the given
WHERE year = 2015;
query. This query will calculate the average
rating of movies from 2015, rounding to 2
decimal places.
Aggregate Functions
Aggregate functions perform a calculation on a
set of values and return a single value:
● COUNT()
● SUM()
● MAX()
● MIN()
● AVG()
