INF: How to Write a Variance Query in SQL Server (175955)
The information in this article applies to:
- Microsoft SQL Server 6.0
- Microsoft SQL Server 6.5
- Microsoft SQL Server 7.0
- Microsoft SQL Server 2000 (all editions)
This article was previously published under Q175955
When you need to make a comparison between a value and the average
for that value in the same category, you can execute a variance query. For
example, you may want to use a variance query when you want to compare the
salary of an employee against the salary of other employees who hold the
same title, or when you want a comparison between the price of a book and
the price of all the other books of the same category.
To continue the book example, you can execute the query below in the pubs
database. Note that this is a correlated subquery, so it will take a while
to run against a very large table.
select
title,
price,
variance = price - (select avg(price)
from titles t2
where t2.type = t1.type)
from titles t1
| Modification Type: | Minor | Last Reviewed: | 3/14/2005 |
|---|
| Keywords: | kbhowto kbusage KB175955 |
|---|
|