Build Your Own ASP.NET 3.5 Website Using C# & VB (61 page)

and the filtering of these groups: GROUP BY and HAVING. These clauses can help you

find answers to questions like, “Which are the departments in my company that

have at least three employees?” and “What is the average salary in each department?”2

When working with groups of data, you’ll usually need to use
aggregate functions
.

Earlier, you learned about simple functions, which receive fixed numbers of parameters as their inputs. Aggregate functions, on the other hand, can handle a variable number of parameters, and can perform a range of tasks with these parameters.

2 Assuming, of course, that your Employees table has a Salary column, or some other way of keeping track of salaries.

Licensed to [email protected]

334

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

The typical example for an aggregate function is COUNT, which is used when we

want to count how many records are returned by a SELECT query. In the following

pages, we’ll learn about the GROUP BY and HAVING clauses, which are useful when

working with aggregate functions; we’ll also explore the COUNT, SUM, AVG, MIN and

MAX functions.

The COUNT Function

The COUNT function returns the number of records selected by a query. If you wanted

to retrieve the total count of employees in your Employees table, you could run the

following query:

SELECT COUNT(Name) AS NumberOfEmployees

Running this query with your current sample data would return the number of

employees stored in the database, as follows:

NumberOfEmployees

----------------6

(1 row(s) affected)

The COUNT function becomes far more useful when it’s combined with a GROUP BY

clause.

Grouping Records Using GROUP BY

Let’s imagine that you need to find out how many employees work in each department. We already know how to get a list of employees and their departments: SELECT Departments.Department, Employees.Name

INNER JOIN Departments ON Departments.DepartmentID =

Employees.DepartmentID

The results of this query are shown below:

Licensed to [email protected]

Speaking SQL

335

Department

Name

---------------------------------------------------------------Executive

Zak Ruvalcaba

Marketing

Jessica Ruvalcaba

Engineering

Ted Lindsey

Engineering

Shane Weebe

Marketing

David Levinson

Accounting

Geoff Kim

(6 row(s) affected)

Now, let’s build on this query to find out how many employees work in each department. Let’s start by adding the COUNT aggregate function: SELECT Departments.Department,
COUNT(Employees.Name) AS

HowManyEmployees

INNER JOIN Departments ON Departments.DepartmentID =

Employees.DepartmentID

If we execute this query as is, we get the following error message:

Msg 8120, Level 16, State 1, Line 1

Column 'Departments.Department' is invalid in the select list

because it is not contained in either an aggregate function or the

GROUP BY clause.

Yikes! What this error message is trying to tell us is that SQL Server is confused. It

knows that we want to count employees, but it doesn’t understand how the Departments.Department field relates to this query. We can tell SQL Server to count the employees based on their departments by adding a GROUP BY clause, like so:

SELECT Departments.Department, COUNT(Employees.Name) AS

FROM Employees

INNER JOIN Departments ON Departments.DepartmentID =

Employees.DepartmentID

GROUP BY Departments.Department

Licensed to [email protected]

336

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

When we run the query now, we get the result we were expecting:

Department

HowManyEmployees

----------------------------------------------------------------Accounting

1

Engineering

2

Executive

1

Marketing

2

(4 row(s) affected)

Filtering Groups Using HAVING

Let’s say that we’re interested only in the members of the Ruvalcaba family that

work at Dorknozzle and that, as before, we want to know how many of them work

in each department. We can filter out those employees using a WHERE clause, as

shown below:

SELECT Departments.Department, COUNT(Employees.Name) AS

FROM Employees

INNER JOIN Departments ON Departments.DepartmentID =

Employees.DepartmentID

WHERE Employees.Name LIKE '%Ruvalcaba'

GROUP BY Departments.Department

While this query is a little complicated, the WHERE clause by itself is pretty simple—it

includes only employees whose names end with Ruvalcaba. These records are the

only ones that are included in the count, as you can see here:

Department

HowManyEmployees

----------------------------------------------------------------Executive

1

Marketing

1

(2 row(s) affected)

When SQL Server processes this query, it uses the WHERE clause to remove records

before counting the number of employees in each department. The HAVING clause

works similarly to the WHERE clause, except that it removes records
after
the aggregate functions have been applied. The following query builds on the previous example.

Licensed to [email protected]

Speaking SQL

337

It seeks to find out which of the departments listed in the Dorknozzle database have

at least two employees:

SELECT Departments.Department, COUNT(Employees.Name) AS

FROM Employees

INNER JOIN Departments ON Departments.DepartmentID =

Employees.DepartmentID

GROUP BY Departments.Department

HAVING COUNT(Employees.Name) >= 2

The results show us that there are two departments that have at least two employees:

Department

HowManyEmployees

----------------------------------------------------------------Engineering

2

Marketing

2

(2 row(s) affected)

The SUM, AVG, MIN, and MAX Functions

Other common aggregate functions you’re likely to need when you’re building more

complex applications include:

SUM

Unlike the COUNT function, which returns a value that reflects the number of

rows returned by a query, the SUM function performs a calculation on the data

within those returned rows.

AVG

The AVG function receives a list of numbers as its arguments, and returns the

average of these numbers.

MIN, MAX

The MIN and MAX functions enable you to find the smallest and largest values in

a group, respectively.

These functions are great for conducting a statistical analysis of records within the

database. For example, it wouldn’t be difficult to use them to put together a webLicensed to [email protected] 338

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

based accounting application that monitored daily sales, and gave us totals, averages,

and the minimum and maximum values for certain products sold.

Updating Existing Data

Okay, so SQL is great for querying existing data. Fantastic! But how are we supposed

to add data to the tables in the first place? We can’t exactly ask Dorknozzle employees

to add data to our tables using SQL Server Management Studio, can we? We need

to learn how to add, update, and delete data inside our database programmatically.

The basic SQL statements that handle these actions are INSERT, UPDATE, and DELETE.

Let’s put them to work!

The INSERT Statement

Here’s a very simple example of INSERT in action:

INSERT INTO Departments (Department)

VALUES ('Cool New Department')

Executing this command adds a new department, named Cool New Department, to

our database. When we add a new row to a table, we must supply data for all the

columns that don’t accept NULL, don’t have a default value, and aren’t IDENTITY

columns that are automatically filled by the database (as in this example).

If, in
Chapter 7, you used the database scripts to create database structures and insert

data, you probably noticed that the script contained many INSERT commands, which

populated the tables with the sample data.

The INSERT statement generally consists of the following components:

INSERT INTO

These keywords indicate that this statement will add a new record to the database. The INTO part is optional, but it can make your commands easier to read.
table name

We provide the name of the table into which we want to insert the values.

Licensed to [email protected]

Speaking SQL

339

column names

We also list the names of the columns for which we’ll be supplying data in this

statement. We separate these column names with commas and enclose the list

in parentheses.

VALUES

This keyword comes between the list of columns and their values.

values

We provide a list of values that we wish to supply for the columns listed above.

Try the above SQL statement. Then, to read the new list of records, execute the

following query:

SELECT DepartmentID, Department

All records in the Departments table will be displayed, along with our Cool New

Department and its automatically generated DepartmentID.

Identity Values

To obtain programmatically the identity value that we just generated, we can use

the scope_identity function like this:

SELECT scope_identity()

The UPDATE Statement

We use the UPDATE statement to make changes to existing records within our database

tables. The UPDATE statement requires certain keywords, and usually a WHERE clause,

in order to modify particular records. Consider this code:

SET Name = 'Zak Christian Ruvalcaba'

WHERE EmployeeID = 1

This statement would change the name of the employee whose EmployeeID is 1.

Let’s break down the UPDATE statement’s syntax:

Licensed to [email protected]