Lets face it. We programmers are lazy. We like to do things the quickest way possible using the least possible effort. The other thing most programmers do a lot is work with databases and SQL. Weather designing the backend functionality on a website, or creating an app that needs to manipulate data form a database, we always end up writing queries to interact with our databases. So the purpose in this tutorial is to make you aware of some functions that are built in to SQL which could make our lives that wee bit easier.

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.

SELECT LEFT('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.

SELECT RIGHT('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.

Conslusion

So we have seen the two different type of functions that can be used in SQL and how they can make our lives that little bit easier. For further reading on SQL functions why not check out these resources:

Just to point out all of the above functions are compatible with MySQL version 5.0+.

Related Posts

  • No Related Posts

More?

If you enjoyed this post, please consider promoting it.
  • Digg
  • del.icio.us
  • StumbleUpon
  • Design Float
  • Reddit
  • Mixx
  • Technorati

Remeber to subscribe to the feed and get future articles delivered to your feed reader.

If you want to discuss this tutorial or any other thoughts you have then you can do so over on our fourm.

Comments


No comments yet...

Leave a Comment

(required)

(required)