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?
you want a GROUP BY for this
select ID, sum(Count) as Sum
from yourTable
where DateTime between startDate and endDate
group by ID
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.
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.
SELECT ID, SUM(Count)
FROM yourTable
WHERE DateTime => '2009-10-01' AND DateTime < '2009-11-01'
GROUP BY ID;
