Our Example Table:

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.

Employees:

Name	Age	Salary(£)	Title
Joe Bloggs	45	80000	CEO
Jack Smith	26	22000	Software Engineer
Mark Ford	32	30000	Software Engineer

Aggregate Functions

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 started.

AVG(column):

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 Employees;

The result should be 81.66. Pretty simple eh!

COUNT(*)/COUNT(column):

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.

SELECT Count(*)FROM Employees WHERE Salary >25000;

The result should be 2.

COUNT DISTINCT:

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)FROM Employees;

The result here should be 2 as Software Engineer is duplicated so it only gets counted once.

MIN(column):

Returns the minimum value of a column. Say we want to find the lowest salary from our employees.

SELECT MIN(Salary)FROM Employees;

This will return the value 22000.

MAX(column):

Returns the maximum value of a column. Pretty much the same as above.

SELECT MAX(Salary)FROM Employees;

This will return the value 80000.

SUM(column):

Returns the total of all the values in a column. So the total age of our employees would be:

SELECT SUM(Age)FROM Employees;

The result should be 103.

FIRST(column):

Returns the first value of the given column. As an example to find the first person in our table above.

SELECT FIRST(Name)FROM Employees;

The result should be Joe Bloggs.

LAST(column):

Returns the last value of the given column. Again much the same as above.

SELECT LAST(Name)FROM Employees;

The result 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.

STDEV(column):

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.

VARIANCE(column):

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.

Scalar Functions:

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 functions.

UPPER(string)/LOWER(string):

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);

This should return the word “Blog”.

LENGTH(string):

Returns the length of the given string.

SELECT LENGTH('Joe Bloggs');

This should return the number 10.

LEFT(string, length):

Returns a string starting from the left of the given string to the given length.

SELECTLEFT('Joe Bloggs',3);

This should return the word “Joe”.

RIGHT(string, length):

Returns a string starting from the right of the given string to the given length.

SELECTRIGHT('Joe Bloggs',6);

This should return the word “Bloggs”.

REPEAT(string, count):

Returns a string containing the given string repeated “count” times.

SELECT REPEAT('Joe Bloggs',3);

This should return the string “Joe BloggsJoe BloggsJoe Bloggs”.

REVERSE(string):

Returns a string with the order of the characters reversed.

SELECT REVERSE('Joe Bloggs');

This should 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.

SELECT ROUND(1.234);

#would return 1

SELECT ROUND(1.234,2);

#would return 1.23

NOW():

Returns the current date and time in the format “YYYY-MM-DD HH:MM:SS”.

SELECT NOW();

This would return the current date and time. For example 2008-08-04 16:50:26