Help with a SQL Query

Question

Hi, I have been trying to think of a way to retrieve some data from a database by way of a SQL query. I am trying to avoid doing it programatically.

essentially the source table is

ID,DateTime,Count

I want to get the sum of the count for a defined period of DateTime for each distinct ID within that period.

Any ideas people?

Answer 1

Try something like this:

select ID, sum(Count)
from foo
where [DateTime] between @beginning and @end
group by ID;

This is assuming that you have two variables, @beginning and @end that are typed as DateTime.

Answer 2

you want a GROUP BY for this

select ID, sum(Count) as Sum
from yourTable
where DateTime between startDate and endDate
group by ID

Answer 3

SELECT ID, SUM(Count) 
FROM yourTable
WHERE DateTime => '2009-10-01' AND DateTime < '2009-11-01' 
GROUP BY ID;

Answer 4

For dates, you should use >= and <, using the start of the period and the start of the next period, like this:

WHERE [DateTime] >= @StartPeriod AND [DateTime] < @StartNextPeriod

..making the whole thing:

SELECT ID, SUM(Count) AS Cnt
FROM dbo.someTable
WHERE [DateTime] >= @StartPeriod AND [DateTime] < @StartNextPeriod
GROUP BY ID;

Otherwise, you run the risk of missing something or including too much.