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.
>

QUERY

Using sql 2000 here is my table, query, and error.
Table:
create table project
(task varchar(14) not null,
startdate datetime,
enddate datetime);
Query:
SELECT TASK "TASKS_SHORTER_THAN_ONE_MONTH"
FROM PROJECT
WHERE ADD_MONTHS (STARTDATE,1) > ENDDATE;
Error:
Server: Msg 195, Level 15, State 10, Line 3
'ADD_MONTHS' is not a recognized function name.I think you want this
SELECT TASK as 'TASKS_SHORTER_THAN_ONE_MONTH'
FROM PROJECT
WHERE datediff(m,STARTDATE, ENDDATE) < 1
of if you wanted days use
WHERE datediff(dd,STARTDATE, ENDDATE) < 30
Download BOL, it will help immensely -
http://www.microsoft.com/downloads/...&DisplayLang=en
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"RMSANDS" <anonymous@.discussions.microsoft.com> wrote in message
news:48E0A71C-7E83-47D6-8838-C4271F0EB6B8@.microsoft.com...
> Using sql 2000 here is my table, query, and error.
> Table:
> create table project
> (task varchar(14) not null,
> startdate datetime,
> enddate datetime);
>
> Query:
> SELECT TASK "TASKS_SHORTER_THAN_ONE_MONTH"
> FROM PROJECT
> WHERE ADD_MONTHS (STARTDATE,1) > ENDDATE;
> Error:
> Server: Msg 195, Level 15, State 10, Line 3
> 'ADD_MONTHS' is not a recognized function name.
>