EXPLANATION
COUNT, SUM, AVG, MAX, and MIN are all built-in functions in SQL.
In SQL a built-in function is a piece for programming that takes zero
or more inputs and returns a value. An example of a built-in functions
is ABS(), which when given a value calculates the absolute
(non-negative) value of the number.
Some
functions, such as ABS() are used to perform calculations, other such
as GETDATE() are used to obtain a system value, such as the current
data, or others, like LEFT(), are used to manipulate textual data.
Here is a simple query using the absolute value function.
SELECT Name,
ABS(500 - ReorderPoint) ReorderPointDeviation
FROM production.Product
WHERE ABS(500 - ReorderPoint) > 200
In
this query we first calculate the difference between 500 and a
product’s reorder point. The ABS function is then used to return the
result as a positive number.
There are several things to note regarding functions.
- The inputs to a function are called parameters. Not all function have parameters, and some functions have more than one.
- Parameters are enclosed in parenthesis.
- We
use functions in the SELECT clause as well as WHERE filter condition. A
function can be used anywhere in a SELECT statement that you can use an
expression.
- Function are reserved words. I would avoid using
them as column or table names. If you do, then expect to qualify your
names with brackets [].
As you learn more about functions
you soon find they are vital in being able to calculate and manipulate
your query results. We’ll dig deeper into specific functions and their
uses in future posts, but before we do so, let’s talk about the type of
data a function can return.
