I am trying to build a report to display stats for each user from multiple data sources. Dataset 2 contains multiple records for each user and I need to return the average of each stat for the month and the number of stats that were calculated to get that average. I am getting incorrect counts. Here's the SQL and results:SELECT m.Name ,m.uid ,MAX(a1.stat1) 'score1' ,MAX(a1.stat2) 'score2 ,AVG(q1.FinalScore) 'score3' ,COUNT(q1.FinalScore) 'countScore3'FROM userlist mLEFT OUTER JOIN dataset1 a1 ON m.uid = a1.uid AND a1.date >= '8/1/2011' AND a1.date < '9/1/2011'LEFT OUTER JOIN dataset2 q1 ON (m.uid = q1.uid) AND (q1.date >= '8/1/2011' AND q1.date < '9/1/2011')WHERE m.uid IN (1,2)GROUP BY m.Name ,m.uidORDER BY m.uid
Sample output is:Name uid score1 score2 score3 countScore3Jason 1 100 98 100 2Steve 2 100 100 100 16
For the daterange 8/1 - 9/1, There are 2 entries in the dataset2 table for Jason that average 100 and 4 entries in the dataset2 table for Steve that average 100, though Jason's row-count is correct at 2 and Steve's shows 16.What could cause this?Am I going about trying to get the count of rows incorrectly?
Query Returning Incorrect Row Count.
Posted - 2011-10-13 : 17:55:40
