Trying to SUM() with a Where Clause and getting improper results..

[kts]

hey, i am trying to sum a bunch of values that are 1's but when it is summed it, it seems to be summing more than just the "Department" and "StudentID" like the where clause states... I'm getting sums of over 100, not sure why ...

 

SELECT q.StudentID StudentID,

q.LastName LastName,

q.FirstName FirstName,

q.Grade Grade,

q.Department Department,

sum(crp.RegentPass)

FROM CredDetails q JOIN CountRegentsPassed crp ON (crp.StudentID = q.StudentID AND crp.Department = q.Department) WHERE crp.StudentID = q.StudentID AND q.Department = crp.Department GROUP BY q.StudentID, q.Department

[fenway]

I don't understand what you mean...

[kts]

Good point, let me clarify a bit.

 

For each student I have 0 and 1's for the RegentPass value. There are about 20 or so per StudentID. Although when using the sum() in this manner, I am getting 74 and 138, 384 for the value of sum(crp.RegentPass)  not sure why, but it seems to be summing incorrectly... its not consistent though.

[mikosiko]

for a start... why are you using a WHERE clause

 

WHERE crp.StudentID = q.StudentID AND q.Department = crp.Department

 

if already you are doing that in you JOIN clause?

FROM CredDetails q JOIN CountRegentsPassed crp 
            ON (crp.StudentID = q.StudentID AND crp.Department = q.Department)

 

clean that first...

 

seems that you should also GROUP BY q.department, q.studenId

[kts]

Yea, I was just getting desperate... The issue still arises, its strange.. they all seem random highest is 192 and just scatters down randomly no rhyme or reason...

 

New query:

SELECT q.StudentID StudentID,

q.LastName LastName,

q.FirstName FirstName,
q.Grade Grade,
q.Department Department,
sum(crp.RegentPass)
FROM CredDetails q JOIN CountRegentsPassed crp ON (crp.StudentID = q.StudentID AND crp.Department = q.Department) GROUP BY q.Department, q.StudentID

[fenway]

Drop the GROUP BY / aggregate part, make sure it's reasonable output