Query ?

Here is the table:
CREATE TABLE [teamstats] (
[name] [varchar] (10) NOT NULL ,
[pos] [varchar] (3) NOT NULL ,
[ab] [numeric](3, 0) NOT NULL ,
[hits] [numeric](4, 0) NOT NULL ,
[walks] [varchar] (5) NOT NULL ,
[singles] [varchar] (7) NOT NULL ,
[doubles] [varchar] (7) NOT NULL ,
[triples] [varchar] (7) NOT NULL ,
[hr] [numeric](2, 0) NOT NULL ,
[so] [varchar] (2) NOT NULL
) ON [PRIMARY]
GO
When I run this:
select sum (singles) "total_singles", sum(doubles) "total_doubles",
sum (triples) "total_triples", sum (hr) "total_hr"
from teamstats
where hits/ab >=.300;
I get this:
Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a varchar data type as an
argument.
Server: Msg 409, Level 16, State 1, Line 1
The sum or average aggregate operation cannot take a varchar data type as an
argument.
Server: Msg 409, Level 16, State 1, Line 1
The sum or average aggregate operation cannot take a varchar data type as an
argument.The error that you receive is a pretty good pointer to the problem.
"The sum or average aggregate operation cannot take a varchar data type =
as an argument."
You cannot perform a SUM on a varchar data type. You need to convert =
the values to int before performing the SUM. This will work as long as =
you have numeric data stored within singles, doubles, and triples.
select sum (CONVERT(int,singles)) AS total_singles, =
sum(CONVERT(int,doubles)) AS total_doubles,
sum (CONVERT(int,triples)) AS total_triples, sum (hr) AS total_hr
from teamstats
where hits/ab >=3D.300
--=20
Keith
"Rmsands" <anonymous@.discussions.microsoft.com> wrote in message =
news:4B521924-DBB1-4262-B1E7-0988FB2F3632@.microsoft.com...
> Here is the table:
>=20
> CREATE TABLE [teamstats] (
> [name] [varchar] (10) NOT NULL ,
> [pos] [varchar] (3) NOT NULL ,
> [ab] [numeric](3, 0) NOT NULL ,
> [hits] [numeric](4, 0) NOT NULL ,
> [walks] [varchar] (5) NOT NULL ,
> [singles] [varchar] (7) NOT NULL ,
> [doubles] [varchar] (7) NOT NULL ,
> [triples] [varchar] (7) NOT NULL ,
> [hr] [numeric](2, 0) NOT NULL ,
> [so] [varchar] (2) NOT NULL=20
> ) ON [PRIMARY]
> GO
>=20
>=20
> When I run this:
>=20
> select sum (singles) "total_singles", sum(doubles) "total_doubles",
> sum (triples) "total_triples", sum (hr) "total_hr"
> from teamstats
> where hits/ab >=3D.300;
>=20
> I get this:
>=20
> Server: Msg 409, Level 16, State 2, Line 1
> The sum or average aggregate operation cannot take a varchar data type =
as an argument.
> Server: Msg 409, Level 16, State 1, Line 1
> The sum or average aggregate operation cannot take a varchar data type =
as an argument.
> Server: Msg 409, Level 16, State 1, Line 1
> The sum or average aggregate operation cannot take a varchar data type =
as an argument.
>