SQL Server Magazine

In their simplest form, SELECT statements let you retrieve data stored in databases. However, you can use them for so much more, including aggregating data. Aggregating data simply means bringing data together and summarizing it. Aggregate functions available in T-SQL include COUNT, MIN, MAX, AVG, and SUM. There are other functions, but for now, I’ll show you how to use these five.

The Prerequisites
To help you follow the examples I present, I created a sample Employee table for you to use. I’ll assume you have a database to work with and the permissions needed to create and modify tables in it. To create the sample Employee table, follow these steps:

1. Download the CodeToCreateEmployeeTable.sql and CodeToPopulateEmployeeTable.sql files. Go to www.sqlmag.com, enter 98315 in the InstantDoc ID text box, and click the 98315.zip hotlink.

2. Create the Employee table. Open SQL Server 2005’s SQL Server Management Studio (SSMS) or SQL Server 2000’s Query Analyzer and copy the code in CodeToCreateEmployeeTable.sql, which Listing 1 shows, in the query window. In the code at callout A in Listing 1, change MyDB to the name of your database. Execute the code.

3. Populate the Employee table. To do this, run CodeToPopulateEmployeeTable.sql. As Listing 2 shows, this code uses single-record INSERT statements to add the fictitious employee data. (See “T-SQL 101, Lesson 2,” April 2008, InstantDoc ID 98105, for information about this type of INSERT statement.)

After you’ve created and populated the Employee table, take a minute to familiarize yourself with the table’s layout.

Counting Records
You can use the COUNT function in a SELECT statement to obtain the number of items in a group. You enclose the item you want to count in parentheses. The item can be just about any expression (i.e., column name, function, constant value, or any combination thereof), but usually the item is a single column in a table. You can specify an asterisk (*) if you want to count all the records in the table. For example, if you want to determine how many records are in the Employee table, you’d run the code

SELECT COUNT(*)
  AS ‘Employees’
  FROM Employee

In this statement, the AS ‘Employees’ clause specifies that you want the results displayed under the column name of Employees. (See “T-SQL 101, Lesson 1,” March 2008, InstantDoc ID 97724, for information about using the AS clause to display different column names in result sets.) The FROM Employee clause specifies the target table. As Figure 1 shows, the results show that the Employee table has 8 records. Suppose that you only want to count the number of employees that make under $30,000 a year. You can add a WHERE clause that specifies the value in the Salary column must be less than $30,000:

 SELECT COUNT(*)
  AS ‘Impoverished’
  FROM Employee
  WHERE Salary < 30000

In this case, the result is 1, which is displayed under the column name of Impoverished.

When you specify *, the COUNT function counts all rows, even if columns within the rows contain NULL values (i.e., entries that have no explicitly assigned value). If you specify a column name, however, only non-NULL values are counted. When you use the DISTINCT keyword, the number of unique non-NULL values is determined. The expression specified must be a column name and not an arithmetic expression. For example, you can use the DISTINCT keyword to determine how many unique last names are in the LastName column in the Employee table:

 SELECT COUNT(DISTINCT LastName)
  AS ‘Last Names’
  FROM Employee

The result is 7 because two of the employees have the same last name (i.e., Smith), as callout A in Listing 2 shows.

Note that at the beginning of this section, I mentioned you can count just about any expression. I said just about because you can’t use expressions of type uniqueidentifier, text, ntext, or image. If you’re unfamiliar with these data types, see the Data Types (Transact-SQL) Web page at msdn2.microsoft.com/ en-us/library/ms187752.aspx.

Determining Minimum Values
When you use the MIN function in a SELECT statement, you can find out the minimum value for a specified column or arithmetic expression, which you enclose in parentheses. For example, to determine the lowest salary in the Employee table, you can use the query

SELECT MIN(Salary)
  AS ‘Minimum Salary’
  FROM Employee

The result is $23,500. Although you can use the DISTINCT keyword with the MIN function, there’s no point. By the function’s very definition, there can be only one value.

Unlike the COUNT function, the MIN function always ignores NULL values. (Similarly, the MAX, AVG, and SUM functions always ignore NULL values.) Like the COUNT function, the MIN function is quite versatile in that you can include just about any expression to specify the item for which you want to find the minimum value. For example, you can use an expression that contains the DATEDIFF and GETDATE functions to determine the number of months that the most-recent hired employee has been with our fictitious company. First, you use DATEDIFF and GETDATE to calculate the number of months that have elapsed since each employee was hired. Then, you use MIN to determine the lowest number out of all the month values just calculated.

Continue to page 2

   Prev. page   [1] 2 3     next page