As you can see, the WHERE clause consists of
two components. The first component—Salary <
(SELECT MAX(Salary) FROM Employee)—uses
MAX to find the maximum salary, then uses the <
(less than) operator to select the salaries lower than that
value. The second component—Salary > (SELECT
MIN(Salary) FROM Employee)—uses MIN to find
the minimum salary, then uses the > (greater than)
operator to select the salaries higher than that value. By
joining these two components with the AND operator, the WHERE statement
retrieves only those
values that are lower
than the maximum
salary and higher than
the minimum salary.
The AVG function then
uses those values to
determine the average
salary. In this case, the
result is $75,166, which is quite a bit lower than previously
reported $90,562. (See Lesson 1 for background
information on how to use the AND, <, and > operators
in WHERE clauses.)
Determining the Sum of Values
When you use the SUM function in a SELECT statement,
you can obtain the total of all the values in
the specified column or arithmetic expression. (The
values being totaled must be numeric.) For example,
the query
SELECT SUM(Salary)
AS ‘Salary Total’
FROM Employee
shows that the sum of all the salaries in the Employee
table is $724,500. Like the AVG function, the data type
returned by the SUM function is determined by the
type of data being evaluated.
With the sum of all the salaries in hand, you
might be tempted to divide that total by the number
of records returned by COUNT(*) to get an average
salary. However, this practice can lead to problems
because COUNT(*) and SUM handle NULL values
differently. As I mentioned previously, COUNT(*)
includes NULL values whereas SUM ignores them.
So, for example, if the HR department didn’t get a
chance to enter all the salary data for employees, using
the SUM and COUNT(*) functions to calculate the
average salary would lead to an inaccurate calculation
because you’d be totaling only the available salary data
and dividing that total by all the employee records.
Thus, it’s safer to use the AVG function instead because
it ignores NULL values altogether.
Beyond the Aggregate Basics
In this lesson, I showed you examples of how to use
the COUNT, MIN, MAX, AVG, and SUM functions
in SELECT queries. Although these queries are useful
from a teaching perspective, they only scratch the surface
when it comes to showing how useful aggregate
functions can be when they’re used in more complex
queries. In Lesson 4, I’ll show you how to tap into the
true power of T-SQL by using aggregate functions with
the Group By clause in SELECT statements.
End of Article
Prev. page
1
2
[3]
next page -->
