kts Posted September 21, 2010 Share Posted September 21, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/213955-trying-to-sum-with-a-where-clause-and-getting-improper-results/ Share on other sites More sharing options...
fenway Posted September 21, 2010 Share Posted September 21, 2010 I don't understand what you mean... Quote Link to comment https://forums.phpfreaks.com/topic/213955-trying-to-sum-with-a-where-clause-and-getting-improper-results/#findComment-1113509 Share on other sites More sharing options...
kts Posted September 21, 2010 Author Share Posted September 21, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/213955-trying-to-sum-with-a-where-clause-and-getting-improper-results/#findComment-1113510 Share on other sites More sharing options...
mikosiko Posted September 21, 2010 Share Posted September 21, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/213955-trying-to-sum-with-a-where-clause-and-getting-improper-results/#findComment-1113520 Share on other sites More sharing options...
kts Posted September 21, 2010 Author Share Posted September 21, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/213955-trying-to-sum-with-a-where-clause-and-getting-improper-results/#findComment-1113525 Share on other sites More sharing options...
fenway Posted September 21, 2010 Share Posted September 21, 2010 Drop the GROUP BY / aggregate part, make sure it's reasonable output Quote Link to comment https://forums.phpfreaks.com/topic/213955-trying-to-sum-with-a-where-clause-and-getting-improper-results/#findComment-1113560 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.