Mysql 9: Functions
Written by: maffelu , 2009-04-16 21:17:35
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'
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:
This will output 39.2500 (which is: 34+67+24+35 / 4).
This function counts the rows of a table.
Syntax:
SELECT COUNT(column)
FROM table_name
Example:
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
This function will output the highest value in a column.
Syntax:
SELECT MAX(column)
FROM table_name
Example:
This will output 67 since our person Silvia is the oldest table member.
Take one guess
This function is the opposite of MAX. It will return the lowest value of a column.
Syntax:
SELECT MIN(column)
FROM person
Example:
This will output 24 since our person Magnus is the youngest table member.
This function sums a column for you.
Syntax:
SELECT SUM(column)
FROM table_name
Example:
This will return 157, the sum of all the table members(34+67+24+32).
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
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).
There are no comments on this article.
Leave a message!
If you have any question or just want to leave a message, just fill out the form below!
Your e-mail will not be visible in your post, it is for validation reasons only
Maffelu