SQL newbie alert...

I have a table with the fields Location, MachineName ,Hours and Date.
(The actual database is more complex but it will do for the purposes of this
post)

I want to return Location, MachineName and the sum of the hours between two
dates. Here is an idea of what I need, although obviously this code doesn't
work.

SELECT Location, MachineName, SUM(Hours)
FROM Table
WHERE Date BETWEEN date1 AND date2

I have experimented with various nested SELECT statements but with no
success. I'm new to SQL and out of my depth already, can anyone explain how
to do this?


Ray Greene.

Don't assume it is obvious what is wrong. It helps to say exactly what
error message you got and also to post enough code to reproduce the
problem - at least a CREATE TABLE statement.

In this case you missed the GROUP BY:

SELECT location, machinename, SUM(hours)
FROM tbl
WHERE date >= '20050101'
AND date < '20060101'
GROUP BY location, machinename ;

It is usually easier to specify date ranges as >= and < unless you are
sure that the DATETIMEs will never contain times other than midnight.

--
David Portas
SQL Server MVP
--

On 12 Oct 2005 03:03:24 -0700, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote:

>Don't assume it is obvious what is wrong. It helps to say exactly what
>error message you got and also to post enough code to reproduce the
>problem - at least a CREATE TABLE statement.

OK, I'll do that in future.

>In this case you missed the GROUP BY:
>
>SELECT location, machinename, SUM(hours)
> FROM tbl
> WHERE date >= '20050101'
> AND date < '20060101'
> GROUP BY location, machinename ;

Well that was simple enough compared to what I was trying to do.

I had assumed that it would take a nested query, I guess I shouldn't jump to
conclusions when I don't know what I'm doing :-)

>It is usually easier to specify date ranges as >= and < unless you are
>sure that the DATETIMEs will never contain times other than midnight.

Good point, I'll do it that way.

Thanks for the help David, it's much appreciated.

-
Ray Greene.