FUNCTIONS


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>

Update, Delete   < <  PREVIOUS   Table of Contents NEXT  > >   Select

Developed with HTML-Kit
Sandersongs Web Tutorials
Contact the Webmasterwith comments.
©2017, by Bill Sanders, all rights reserved.
This domain had 3,963 different visits in the last 30 days.
435,438 hits on this domain since 24 Nov 2006.
http://sandersongs.com/PHPsqlCourse/SQL05.php
This page was last modified on our server on 4 Jun 2008
and last refreshed on our server at 12:26 pm, MST
This file took 0.00851 seconds to process.