## 31 Oct Sql server tutorial 10 Working with Functions and Value Expressions

## Key Skills & Concepts

- Use Set Functions
- Use Value Functions
- Use Value Expressions
- Use Special Values

In earlier parts of the book, you have been briefly introduced to various value-related

functions and expressions. These values and expressions are used in examples and Try

This exercises in a number of chapters to demonstrate different components of SQL. In

this chapter, I take a closer look at many of these values and expressions, focusing on

those that you are most likely to use as a beginning SQL programmer. You should keep in

mind, however, that this chapter covers only a portion of the many types of functions and

expressions supported by SQL. In addition, SQL implementations can vary greatly with

regard to which SQL functions and expressions they support, how those values and

expressions are implemented, and what nonstandard functions and expressions they

include in their products in addition to the standard ones. Be sure to check the product

documentation to determine what functionality is supported. In general, I include in this

chapter those functions and expressions most commonly supported by SQL

implementations.

## Use Set Functions

In Chapter 3, I introduce the concept of a function. As you might recall, a function is a

named operation that performs predefined tasks that you can’t normally perform by using

SQL statements alone. It is a type of routine that takes input parameters, which are

enclosed in parentheses, and returns values based on those parameters. An important

property of functions is that each execution of a function returns exactly one data value,

and this is why functions can be used in place of table column names in the SELECT list

of a query—the function returns a single value for each row processed by the query. You

have already seen examples of functions, such as SUM and AVG. Both of these functions

are known as set functions. A set function, sometimes referred to as an aggregate function,

processes or calculates data and returns the appropriate values. Set functions require that

the data be grouped in some way, such as would be the case if the GROUP BY clause

were used in a SELECT statement. If the rows are not explicitly grouped in some way, the

entire table is treated as one group.

In this section, I discuss five set functions: COUNT, MAX, MIN, SUM, and AVG.

These functions are all commonly supported in SQL implementations. For all the set

functions, I provide examples of how you would use them in the SELECT clause of a

SELECT statement. The examples are based on the table shown in Figure 10-1.

## Using the COUNT Function

The first set function that we’ll look at is the COUNT function. As the name implies, the

COUNT function counts the number of rows in a table or the number of values in a

column, as specified in a SELECT statement. When you use the COUNT function, you

must specify a column name to count the number of non-null values in a column, or an

asterisk to count all the rows in a table regardless of null values. For example, if you want

to know the total number of rows in the ARTIST_CD_SALES table, you can use the

following.

In this statement, the COUNT function is used with an asterisk—in parentheses—to

count every row in the ARTIST_CD_SALES table and return the total count. The returned

value is listed in the TOTAL_ROWS column, a name given to the column returned in the

query results, as shown in the following results:

As you can see, the query results include only one value (one row with one column), as

can be expected of a set function used without any row grouping. The value of 10

indicates that the ARTIST_CD_SALES table contains 10 rows.

As with any other sort of SELECT statement, you can qualify your query results by

adding the necessary clauses to the statement. For example, suppose you want to find out

how many rows include a NUMBER_SOLD value greater than 20. You can modify your

SELECT statement to include a WHERE clause:

The value returned will now be 7 rather than 10 because only seven rows meet the

search condition specified in the WHERE clause.

You might find that instead of querying the number of rows in a table, you want to

know the number of values in a given column (excluding null values). In this case, you

would specify the column name rather than the asterisk.

When you execute this query, the value returned is again 7. This means that seven

ARTIST_NAME values have a NUMBER_SOLD value greater than 20. However, this

statement doesn’t account for ARTIST_NAME values that might be duplicated. If you

want to arrive at a count that takes into consideration duplicate values, you can add the

DISTINCT keyword to the COUNT function:

This time, a value of 6 is returned rather than 7. This is because the ARTIST_NAME

column includes two instances of the Stevie Wonder value. The column contains only six

unique values that meet the condition set forth in the search criteria.

0 >1>2>3>4>5>6>7>8>9>10>11>12>13>14>15>16>17>18>19>20>

Share the post "Sql server tutorial 10 Working with Functions and Value Expressions"

Sorry, the comment form is closed at this time.