Chapter 8: Speaking SQL
DATEADD
adds an interval to an existing date (a number of days, weeks, etc.) in order to obtain a new date
DATEDIFF
calculates the difference between two specified dates
DATEPART
returns a part of a date (such as the day, month, or year)
DAY
returns the day number from a date
MONTH
returns the month number from a date
YEAR
returns the year from a date
We won’t be working with these functions in our example application, but it’s good to keep them in mind. Here’s a quick example that displays the current year:
SELECT YEAR(GETDATE())
The result (assuming it’s still 2006, of course) is shown below:
CurrentYear
-----------
2006
(1 row(s) affected)
Working with Groups of Values
Transact-SQL includes two very useful clauses that handle the grouping of records, 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
2 Assuming, of course, that your Employees table has a Salary column, or some other way of keeping track of salaries.
318
The COUNT Function
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.
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
FROM Employees
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 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
FROM Employees
INNER JOIN Departments ON Departments.DepartmentID =
Employees.DepartmentID
319
Chapter 8: Speaking SQL
The results of this query are shown below:
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
FROM Employees
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 Department.Departments field relates to this. 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
HowManyEmployees
FROM Employees
INNER JOIN Departments ON Departments.DepartmentID =
Employees.DepartmentID
GROUP BY Departments.Department
When we run the query now, we get the result we were expecting:
Department |
HowManyEmployees |
------------------------------------------------- |
---------------- |
320
Filtering Groups Using HAVING
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
HowManyEmployees
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 with names that end with Ruvalcaba. These records are the only ones that are included in the count, as shown 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. 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
HowManyEmployees
FROM Employees
INNER JOIN Departments ON Departments.DepartmentID =
Employees.DepartmentID
321