Functions

With SQL and SQL Server you can use lots of built-in functions or you may create your own functions. Here we will learn to use some of the most used built-in functions and in addition we will create our own function.

Built-in Functions

SQL has many built-in functions for performing calculations on data.

We have 2 categories of functions, namely aggregate functions and scalar functions. Aggregate functions return a single value, calculated from values in a column, while scalar functions return a single value, based on the input value.

Aggregate functions – examples:

• AVG() – Returns the average value
• STDEV() – Returns the standard deviation value
• COUNT() – Returns the number of rows
• MAX() – Returns the largest value
• MIN() – Returns the smallest value
• SUM() – Returns the sum
•  

Scalar functions – examples:

• UPPER() – Converts a field to upper case
• LOWER() – Converts a field to lower case
• LEN() – Returns the length of a text field
• ROUND() – Rounds a numeric field to the number of decimals specified
• GETDATE() – Returns the current system date and time
•  

• String Functions

Here are some useful functions used to manipulate with strings in SQL Server:

• CHAR
• CHARINDEX
• REPLACE
• SUBSTRING
• LEN
• REVERSE
• LEFT
• RIGHT
• LOWER
• UPPER
• LTRIM
• RTRIM

Read more about these functions in the SQL Server Help.

• Date and Time Functions

Here are some useful Date and Time functions in SQL Server:

• DATEPART
• GETDATE
• DATEADD
• DATEDIFF
• DAY
• MONTH
• YEAR
• ISDATE

Read more about these functions in the SQL Server Help.

• Mathematics and Statistics Functions

Here are some useful functions for mathematics and statistics in SQL Server:

• COUNT
• MIN, MAX
• COS, SIN, TAN
• SQRT
• STDEV
• MEAN
• AVG

Read more about these functions in the SQL Server Help.

• AVG()

The AVG() function returns the average value of a numeric column. Syntax:

SELECT AVG(column_name) FROM table_name

Example:

Given a GRADE table:

We want to find the average grade for a specific student:

select AVG(Grade) as AvgGrade from GRADE where StudentId=1

• COUNT()

The COUNT() function returns the number of rows that matches a specified criteria.

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT(column_name) FROM table_name

The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name

We use the CUSTOMER table as an example:

• The GROUP BY Statement

Aggregate functions often need an added GROUP BY statement.

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

Syntax

SELECT column_name, aggregate_function(column_name) 
FROM table_name
WHERE column_name operator value 
GROUP BY column_name

Example:

We use the CUSTOMER table as an example:

If we try the following:

select FirstName, MAX(AreaCode) from CUSTOMER

We get the following error message:

Column ‘CUSTOMER.FirstName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The solution is to use the GROUP BY:

select FirstName, MAX(AreaCode) from CUSTOMER
group by FirstName

• The HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Syntax:

SELECT column_name, aggregate_function(column_name) 
FROM table_name
WHERE column_name operator value 
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

We use the GRADE table as an example:

First we use the GROUP BY statement:

While the following query: