MorkaLork Development

Interesting stuff I've picked up over the years...

Mysql 9: Functions

2009-04-16 21:17:35 | 141 views | sql mysql table function inserting average max count min sum

The average SQL database has some functions to work with the data besides showing and inserting. You can do math and/or compara data, use substring or trim etc. This article will show you some of the general functions. Different SQL variants may have different functions, but these should be the basic ones included:


This will be our table 'person'

|--------------------+--------------------+--------------------|
|Name |Age |Gender |
|____________________+____________________+____________________|
|Bob 34 Male |
|Silvia 67 Female |
|Magnus 24 Male |
|Lisa 32 Female |
+--------------------------------------------------------------+


AVG:



The Average method gives you an average sum based upon a column in a table. Average must be used with a numerical value. No error will be shown if used on another datatype(say, VARCHAR) but this will output a default '0'.

Syntax:

SELECT AVG(column)
FROM table_name


Example:


SELECT AVG(Age)
FROM person


This will output 39.2500 (which is: 34+67+24+35 / 4).


COUNT:



This function counts the rows of a table.

Syntax:

SELECT COUNT(column)
FROM table_name


Example:


SELECT COUNT(Name)
FROM person


This will output 4 since we have 4 rows of data.

COUNT can, however, also be used together with the keyword DISTINCT creating a method that only counts unique rows. If we had, say, two persons with the name Bob but differant age, only one would count if we did


SELECT COUNT(DISTINCT Name)
FROM person



MAX:



This function will output the highest value in a column.

Syntax:

SELECT MAX(column)
FROM table_name


Example:


SELECT MAX(Age)
FROM person


This will output 67 since our person Silvia is the oldest table member.

MIN:



Take one guess =D
This function is the opposite of MAX. It will return the lowest value of a column.

Syntax:

SELECT MIN(column)
FROM person


Example:


SELECT MIN(Age)
FROM person


This will output 24 since our person Magnus is the youngest table member.


SUM:



This function sums a column for you.

Syntax:

SELECT SUM(column)
FROM table_name


Example:


SELECT SUM(Age)
FROM person


This will return 157, the sum of all the table members(34+67+24+32).




Article comments

Feel free to comment this article using a facebook profile.

I'm using facebook accounts for identification since even akismet couldn't handle all the spam I receive every day.