Intro to SQL and Relational Databases: Aggregate Functions
-
Overview
- Functions
- Aggregate Functions
-
SELECT with functions
In SQL, functions are stored programs that you can pass parameters into and then return a value. We can use these built-in SQL functions in our select statements like:
- MIN()
- MAX()
- COUNT()
- AVG()
- SUM()
-
SELECT with functions syntax
SELECT FUNC(column_name)
FROM table;
-
SELECT with functions example
SELECT MIN(year)
FROM cars;
returns
| MIN(YEAR) |
|---|
| 2017 |
-
Aggregate Functions
Aggregate functions allow you to run a function down a column rather than on each particular row. Some of these include
- COUNT
- MAX
- MIN
- SUM
-
Aggregate Functions: Count
Count will give you the total number of rows in a table based on a column.
SELECT COUNT(*) as "count"
FROM teachers;
| count | |:—–:| | 3 |
Count does not consider the values in any column. It will simply add one for each row.
-
Aggregate Functions: Max/Min
MAX and MIN will give you the largest or smallest value of a column in any row.
SELECT MAX(years) as "max", MIN(years) as "min"
FROM zipcode.teacher_meta
| max | min |
|---|---|
| 11 | 4 |
MAX and MIN will consider data type when doing this sort. Text will sort lexographically and numbers will sort numerically.
-
Aggregate Functions: Sum
SUM will take the value of a numeric column and add together all of the values.
SELECT SUM(years) as "sum"
FROM zipcode.teacher_meta
| sum |
|---|
| 19 |
-
Aggregate Functions - Group Concat
GROUP_CONCAT will take a column and concatenate all the rows.
SELECT GROUP_CONCAT(' ', first_name, ' ', last_name) as "group_concat"
FROM zipcode.teachers;
| group_concat |
|---|
| John Smith, Tabitha Schultz, Jane Herman |
-
Group By
Sometimes you want to take an aggregate of rows but you don’t want to indiscriminately aggregate all rows. You want to group some rows together and aggregate those.
Right now in order to do that you could try using a WHERE clause, but you’d have to know some information about the rows beforehand. Instead we may group a column with a GROUP BY.
Lets write a query to list teachers who have equivilent experience together.
-
Group By
SELECT GROUP_CONCAT(' ', first_name, ' ', last_name) as "teachers", years
FROM zipcode.teachers t
JOIN zipcode.teacher_meta tm
ON t.id = tm.teacher_id
GROUP BY tm.years;
| teachers | years | |:—————————-|:—–:| | John Smith, Tabitha Schultz | 4 | | Jane Herman | 11 |
-
Having
A HAVING clause can be used to filter the results of a query. This is similar to the Where clause, but it works only with fields that are Aggregates.
Lets Write a query to find all students who have been assigned more than one assignemnt.
-
HAVING
SELECT s.name, COUNT(a_s.assignment_id) as "assignments given"
FROM zipcode.students s
JOIN zipcode.assignment_student a_s
ON s.id = a_s.student_id
GROUP BY s.name
HAVING COUNT(a_s.assignment_id) > 1;
| name | assignments given |
|---|---|
| Linnell McLanachan | 2 |
-
HAVING
HAVING essentially lets you have aggregate functions in your WHERE clause.
It must be used in conjunction with a GROUP BY clause.
-
HAVING syntax
SELECT *
FROM table
GROUP BY column
HAVING COUNT(*) condition;
-
HAVING example
Consider we have the following cars table:
| ID | MAKE | MODEL | YEAR | MILEAGE |
|---|---|---|---|---|
| 8 | Ford | Focus | 2017 | null |
| 7 | Honda | CRV | 2017 | null |
| 1 | Honda | Civic | 2001 | 0 |
| 2 | Mazda | 3 | 2017 | 0 |
| 5 | Mazda | 6 | 2017 | null |
| 6 | Volkswagen | GTI | 2017 | null |
| 3 | Volkswagen | Jetta | 2017 | 0 |
-
HAVING example
SELECT make
FROM cars
GROUP BY make
HAVING COUNT(make) > 1;
This will return just Honda, Mazda, and Volkswagen since Ford only has 1 model in our DB.
-
Let’s review
-
Common aggregate functions:
The following are the most commonly used aggregate functions that are shared across different database server types.
| Name | Description |
|---|---|
| AVG() | Return the average value of the argument |
| COUNT() | Return a count of the number of rows returned |
| COUNT(DISTINCT) | Return the count of a number of different values |
| GROUP_CONCAT() | Return a concatenated string |
| MAX() | Return the maximum value |
| MIN() | Return the minimum value |
| SUM() | Return the sum |
-
Statistical functions:
The following are common statistical functions found across database types:
| Name | Description |
|---|---|
| STD() | Return the population standard deviation |
| STDDEV() | Return the population standard deviation |
| STDDEV_POP() | Return the population standard deviation |
| STDDEV_SAMP() | Return the sample standard deviation |
| VAR_POP() | Return the population standard variance |
| VAR_SAMP() | Return the sample variance |
| VARIANCE() | Return the population standard variance |
-
Additional functions:
The JSON functions are available only after MySQL version 5.7.22, and have different function names in other database types.
| Name | Description |
|---|---|
JSON_ARRAYAGG() (introduced 5.7.22) |
Return result set as a single JSON array |
JSON_OBJECTAGG() (introduced 5.7.22) |
Return result set as a single JSON object |
The bitwise functions also vary according to database server type:
| Name | Description |
|---|---|
| BIT_AND() | Return bitwise AND |
| BIT_OR() | Return bitwise OR |
| BIT_XOR() | Return bitwise XOR |
-
Checking your MySQL Version
To check the version of the MySQL you have installed, log into your server and type the following:
SHOW VARIABLES LIKE "%version%";
You should expect a response that appears similar to this one:
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.21 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.21 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | macos10.13 |
+-------------------------+------------------------------+
8 rows in set (0.01 sec)
-
DBA corgis hope you’re HAVING a blast!