SQL Functions   «Prev  Next»

Lesson 2 Function overview
Objective Understand Issues related to the Use of SQL Functions

SQL Function Overview

When you use functions, it is not likely that you will just be adding values together. What you will be doing, however, is using the built-in functions to perform more helpful work with your database table values. You might recall from the discussion of views that there are cases in which a view is updateable and cases in which it is read-only. If you create a view that includes a function, the view will not be updateable. Since the value returned from the view is actually a formula result, it is not possible to update the values that make up the result. This will force your view to be read-only if you use a function.
Also, when you have a function return a value, there will, by default, be no column header for that value. Remember, you can name the column by indicating the column name in your query:

SELECT 'ColumnName'=Function...

Here is an example:
SELECT 'Name'=au_Fname + '' + au_Lname 
FROM Authors

This results in a derived column, Name, that will contain the results from adding the two columns together.

aggregate function count()

The aggregate function count() counts the number of rows in each group, and the asterisk tells the server to count everything in the group. Using the combination of a group by clause and the count() aggregate function, you are able to generate exactly the data needed to answer the business question without having to look at the raw data. When grouping data, you may need to filter out undesired data from your result set based on groups of data rather than based on the raw data. Since the group by clause runs after the where clause has been evaluated, you cannot add filter conditions to your where clause for this purpose. For example, here is an attempt to filter out any cases where an employee has opened fewer than five accounts:

mysql> SELECT open_emp_id, COUNT(*) how_many
-> FROM account
-> WHERE COUNT(*) > 4
-> GROUP BY open_emp_id;
ERROR 1111 (HY000): Invalid use of group function

You cannot refer to the aggregate function count(*) in your where clause, because the groups have not yet been generated at the time the where clause is evaluated. Instead, you must put your group filter conditions in the having clause. Here's what the query would look like using having:

mysql> SELECT open_emp_id, COUNT(*) how_many
-> FROM account
-> GROUP BY open_emp_id
-> HAVING COUNT(*) > 4;
| open_emp_id | how_many |
| 1 | 8 |
| 10 | 7 |
| 16 | 6 |
3 rows in set (0.00 sec)