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
Showing posts with label average. Show all posts
Showing posts with label average. Show all posts
Friday, February 24, 2012
Conditional Average
Friday, February 17, 2012
Concurrent Connections
HI All
Could help me out to find the following information from the Query
Analyzer?
Number of Concurrent Connections/Users
Average Transaction Per/Sec
Database Growth Rate
RegardsHi,
You can querry system table master..sysprocesses and get required counters.
Amol Lembhe
"Praveen" wrote:
> HI All
> Could help me out to find the following information from the Query
> Analyzer?
> Number of Concurrent Connections/Users
> Average Transaction Per/Sec
> Database Growth Rate
> Regards
>
Could help me out to find the following information from the Query
Analyzer?
Number of Concurrent Connections/Users
Average Transaction Per/Sec
Database Growth Rate
RegardsHi,
You can querry system table master..sysprocesses and get required counters.
Amol Lembhe
"Praveen" wrote:
> HI All
> Could help me out to find the following information from the Query
> Analyzer?
> Number of Concurrent Connections/Users
> Average Transaction Per/Sec
> Database Growth Rate
> Regards
>
Labels:
analyzer,
average,
concurrent,
connections,
database,
following,
microsoft,
mysql,
number,
oracle,
query,
server,
sql,
transaction,
users
Subscribe to:
Posts (Atom)