is to make you aware of some functions that are built in to SQL.
This is the
example table I will be taking all my data from for the rest of the article. So
I have put it here so you can use it as a reference instead of duplicating it
throughout the article.
There are two
types of functions used in SQL. Aggregate Functions and Scalar Functions. An
aggregate function helps summarize data. Its pretty simple really. Want to count
how many online friends you have? Simple. Use the COUNT function. Want to find
our which one of them is the youngest? Easy. Use the MIN function. So lets get
Gives you the average value of the column. So using our table above lets
find the average age of our employees.
SELECT AVG(Age) FROM
should be 81.66. Pretty simple eh!
Counts the number of selected rows/rows with data in a column. This one
is pretty self explanatory. Do remember though if you specify a column to be
counted (i.e. not *) it will only count rows with data in them. So as an example
lets find out how many employees have a salary over 25000.
WHERE Salary >
should be 2.
Here is a useful one. This counts only distinct values in a column and ignores
duplicates. So in our example above we might want to find out how many different
job titles there are.
SELECT COUNT(DISTINCT Title)
here should be 2 as Software Engineer is duplicated so it only gets counted
Returns the minimum value of a column. Say we want to find the lowest
salary from our employees.
SELECT MIN(Salary) FROM
return the value 22000.
Returns the maximum value of a column. Pretty much the same as above.
SELECT MAX(Salary) FROM
return the value 80000.
Returns the total of all the values in a column. So the total age of our
employees would be:
SELECT SUM(Age) FROM
should be 103.
Returns the first value of the given column. As an example to find the first
person in our table above.
SELECT FIRST(Name) FROM
should be Joe Bloggs.
Returns the last value of the given column. Again much the same as above.
SELECT LAST(Name) FROM
should be Mark Ford.
Right so we
have learned a few functions in SQL that maybe we didn’t know. They help us do
simple things faster. The following functions are a bit more powerful and
obscure but just as useful as above.
Returns the Standard Deviation of the given column. Note you have to have at
least two values or the value returned will be null.
SELECT STDEV(Salary) AS
StndDevSalary FROM Employees;
Note: As of
MySQL 5.0.3, the standard SQL function STDDEV_POP() can be used instead.
Returns the Variance of the given column. Note you have to have at least two
values or the value returned will be null.
SELECT VARIANCE(Salary) AS
SarSalary FROM Employees;
Note: As of
MySQL 5.0.3, the standard SQL function VAR_POP() can be used instead.
So we have
just seen some Aggregate functions. Scalar functions are functions that only
take one input value rather than a set of data. So here are some Scalar
Changes a string to upper case/lower case. So using a name from our table.
SELECT UPPER('Joe Bloggs');
#would return JOE BLOGGS
SELECT LOWER('Joe Bloggs');
#would return joe bloggs
MID(string, pos [, length]):
Returns a set number of characters from a string. Takes 3 parameters which are a
string, the starting character and the length of the string you want to return.
SELECT MID('Joe Bloggs',5,4);
return the word “Blog”.
Returns the length of the given string.
SELECT LENGTH('Joe Bloggs');
return the number 10.
Returns a string starting from the left of the given string to the given length.
SELECT LEFT('Joe Bloggs', 3);
return the word “Joe”.
Returns a string starting from the right of the given string to the given
SELECT RIGHT('Joe Bloggs', 6);
return the word “Bloggs”.
Returns a string containing the given string repeated “count” times.
SELECT REPEAT('Joe Bloggs',
return the string “Joe BloggsJoe BloggsJoe Bloggs”.
Returns a string with the order of the characters reversed.
SELECT REVERSE('Joe Bloggs');
return the string “sggolB eoJ”.
ROUND(value [, decimal]):
Returns a the value rounded to “decimal” places. If decimal is not set then
values are rounded to 0 decimal places.
#would return 1
#would return 1.23
Returns the current date and time in the format “YYYY-MM-DD HH:MM:SS”.
return the current date and time. For example 2008-08-04 16:50:26