FUNCTIONS
There are several mathematical functions you may find useful in SQL. To try them, let's first create a table with numbers (remember, do not put quotes around numerical values):
mysql> create table numbers (
-> ID char (8) NOT NULL,
-> AGE char (2) NOT NULL,
-> YRS_OF_SERVICE char (2) NOT NULL,
-> SCORE char (3) NOT NULL,
-> primary key (ID)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into numbers values ('rjones', 37, 14, 89),
-> ('tjones', 34, 16, 75), ('bsmith', 28, 10, 91),
-> ('nsmith', 43, 17, 85);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from numbers;
+--------+-----+----------------+-------+ | ID | AGE | YRS_OF_SERVICE | SCORE | +--------+-----+----------------+-------+ | rjones | 37 | 14 | 89 | | tjones | 34 | 16 | 75 | | bsmith | 28 | 10 | 91 | | nsmith | 43 | 17 | 85 | +--------+-----+----------------+-------+4 rows in set (0.00 sec) mysql>
You can count the number of records in a given selection using the COUNT() function. Try it.
mysql> select count(*) from numbers;
+----------+ | count(*) | +----------+ | 4 | +----------+1 row in set (0.05 sec) mysql> select count(*) from numbers where AGE > 35;
+----------+ | count(*) | +----------+ | 2 | +----------+1 row in set (0.00 sec) mysql> select count(*) from numbers where AGE > 35 and -> YRS_OF_SERVICE < 15;
+----------+ | count(*) | +----------+ | 1 | +----------+1 row in set (0.00 sec) mysql>
You can find the average of the numbers in a selection using the AVG() function. Try it.
mysql> select avg(age) from numbers;
+----------+ | avg(age) | +----------+ | 35.5 | +----------+1 row in set (0.00 sec) mysql> select avg(score) from numbers where yrs_of_service > 15;
+------------+ | avg(score) | +------------+ | 80 | +------------+1 row in set (0.00 sec) mysql> select avg(age), avg(yrs_of_service), avg(score) from numbers;
+----------+---------------------+------------+ | avg(age) | avg(yrs_of_service) | avg(score) | +----------+---------------------+------------+ | 35.5 | 14.25 | 85 | +----------+---------------------+------------+1 row in set (0.00 sec) mysql>
You can find the highest or lowest number in a field using the MAX() and MIN() functions. Try them both.
mysql> select max(score) from numbers;
+------------+ | max(score) | +------------+ | 91 | +------------+1 row in set (0.00 sec) mysql> select max(age), min(yrs_of_service), max(score) from numbers;
+----------+---------------------+------------+ | max(age) | min(yrs_of_service) | max(score) | +----------+---------------------+------------+ | 43 | 10 | 91 | +----------+---------------------+------------+1 row in set (0.00 sec) mysql> select min(yrs_of_service) from numbers where age < 40;
+---------------------+ | min(yrs_of_service) | +---------------------+ | 10 | +---------------------+1 row in set (0.00 sec) mysql>
You can sum the values in a selected column using the SUM() function. Try it.
mysql> select sum(yrs_of_service) from numbers;
+---------------------+ | sum(yrs_of_service) | +---------------------+ | 57 | +---------------------+1 row in set (0.00 sec) mysql> select sum(yrs_of_service) from numbers where score > 85;
+---------------------+ | sum(yrs_of_service) | +---------------------+ | 24 | +---------------------+1 row in set (0.00 sec) mysql>
