Why are these aggregate sums different?

I have a table that looks like this:

CREATE TABLE [dbo].[StudentTime]
([StudentTimeID] [int] NOT NULL IDENTITY(164352, 1),
 [StudentID] [int] NOT NULL,
 --Some columns...
 [EntryDateTime] [datetime] NOT NULL,
 [Hours] [decimal] (6, 2) NOT NULL CONSTRAINT [DF_StudentTime_Hours] DEFAULT ((0))
 --Some more columns
 )

When I query it

SELECT COUNT(DISTINCT StudentID) AS StudentCount,
SUM(HOURS) AS TotalHours
FROM dbo.StudentTime
WHERE EntryDateTime >= '5/1/2010'
AND EntryDateTime < '5/1/2011'

I get this result:

StudentCount: 9890
TotalHours: 775645.5

Now I want to filter students out of the count, who for whatever reason, have accumulated 0 hours in that reporting period:

SELECT COUNT(DISTINCT StudentID) AS StudentCount,
SUM(HOURS) AS TotalHours
FROM dbo.StudentTime
WHERE EntryDateTime >= '5/1/2010'
AND EntryDateTime < '5/1/2011'
AND Hours > 0

I get this result:

StudentCount: 9792 --Expected to be smaller.
TotalHours: 775699.25 --Expected to be same, but is larger?!?!

开发者_StackOverflow中文版

Is it because I have AND Hours > 0 (An INT) vs AND Hours > 0.0 (a DECIMAL)? When I do this where statement, is there an implicit CAST going on?

---

If the sum is larger when you exclude certain records, then you must be excluding negative numbers.

Keep in mind that they sum of [1, -1, 2] is 2, since 1 + (-1) + 2 == 1 - 1 + 2.

As you point out, if the end date and start date are not correct, then you may also exclude records you intended to be included. This may result in a larger or smaller sum than expected as well.

---

You must have some negative hours.

---

Are you working with live data? Even if there are students with negative hours, the distinct student count should have decreased when you added another condition (hours > 0)

OK, I fell for your comment instead of the data. The second result is smaller than the first.