SQL Server 2000
I have a table with an 'annual_inc' column, 7043 records.
I'd like to calculate an average on this column but I do not want to
include annual incomes below 20,000.
Any good way of doing that in a cursor or straight SQL?
Regards
Josh Francis
jwfrancis@.bbandt.comThis is a multi-part message in MIME format.
--=_NextPart_000_013C_01C36E1B.F12063F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
select
avg (annual_inc) as AvgIncome
from
MyTable
where
annual_inc >=3D 20000
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Joshua" <jfrancis@.bbandt.com> wrote in message =news:6407b746.0308290648.24f9962b@.posting.google.com...
SQL Server 2000
I have a table with an 'annual_inc' column, 7043 records.
I'd like to calculate an average on this column but I do not want to
include annual incomes below 20,000.
Any good way of doing that in a cursor or straight SQL?
Regards
Josh Francis
jwfrancis@.bbandt.com
--=_NextPart_000_013C_01C36E1B.F12063F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1226" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#c0c0c0>
<DIV><FONT face=3DTahoma size=3D2>Try:</FONT></DIV>
<DIV><FONT face=3DTahoma size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>select</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2> avg =(annual_inc) as AvgIncome</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>from</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2> =MyTable</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>where</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2> annual_inc =>=3D 20000</FONT></DIV>
<DIV><BR>-- <BR>Tom</DIV>
<DIV> </DIV>
<DIV>---<BR>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL =Server Professional<BR>Toronto, ON Canada<BR><A href=3D"www.pinnaclepublishing.com=">http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"Joshua" <<A href=3D"mailto:jfrancis@.bbandt.com">jfrancis@.bbandt.com</A>> wrote in =message <A href=3D"news:6407b746.0308290648.24f9962b@.posting.google.com">news:6407b7=46.0308290648.24f9962b@.posting.google.com</A>...</DIV>SQL Server 2000<BR><BR>I have a table with an 'annual_inc' column, 7043 records.<BR><BR>I'd like to calculate an average on this column but I do =not want to<BR>include annual incomes below 20,000.<BR><BR>Any good way of =doing that in a cursor or straight SQL?<BR><BR>Regards<BR><BR>Josh =Francis<BR><A href=3D"mailto:jwfrancis@.bbandt.com">jwfrancis@.bbandt.com</A></BODY></HTM=L>
--=_NextPart_000_013C_01C36E1B.F12063F0--|||From the details given this should work.
CREATE TABLE CalcAvg
(
ColToCalc int
)
INSERT CalcAvg VALUES(1000)
INSERT CalcAvg VALUES(2000)
INSERT CalcAvg VALUES(3000)
INSERT CalcAvg VALUES(1000)
select avg(ColToCalc) FROM CalcAvg
WHERE ColToCalc >1000
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment