Query with multiple left joins - points column value is incorrect

Question

I have the following database structure, and I am trying to run a single query that will show classrooms and how many students are part of the classroom, and how many rewards a classroom has allocated out, as well as how many points allocated to a single classroom (based on the classroom_id column).

Using the query at the very bottom I am trying to collect the 'totalPoints' that a classroom has assigned - based on counting the points column in the classroom_redeemed_codes table and return this as a single integer.

For some reason the values are incorrect for the totalPoints - I am doing something wrong but not sure what...

-- UPDATE -- Here is the sqlfiddle:- http://sqlfiddle.com/#!2/a9f45

My Structure:

CREATE TABLE `organisation_classrooms` (
  `classroom_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `active` tinyint(1) NOT NULL,
  `organisation_id` int(11) NOT NULL,
  `period` int(1) DEFAULT '0',
  `classroom_bg` int(2) DEFAULT '3',
  `sortby` varchar(6) NOT NULL DEFAULT 'points',
  `sound` int(1) DEFAULT '0',
  PRIMARY KEY (`classroom_id`)
);

CREATE TABLE organisation_classrooms_myusers (
  `classroom_id` int(11) NOT NULL,
  `user_id` bigint(11) unsigned NOT NULL,
);

CREATE TABLE `classroom_redeemed_codes` (
  `redeemed_code_id` int(11) NOT NULL AUTO_INCREMENT,
  `myuser_id` bigint(11) unsigned NOT NULL DEFAULT '0',
  `ssuser_id` bigint(11) NOT NULL DEFAULT '0',
  `classroom_id` int(11) NOT NULL,
  `order_product_id` int(11) NOT NULL DEFAULT '0',
  `order_product_images_id` int(11) NOT NULL DEFAULT '0',
  `date_redeemed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `points` int(11) NOT NULL,
  `type` int(1) NOT NULL DEFAULT '0',
  `notified` int(1) NOT NULL DEFAULT '0',
  `inactive` tinyint(3) NOT NULL,
   PRIMARY KEY (`redeemed_code_id`),
);

SELECT
  t.classroom_id,
  title,
  COALESCE (
    COUNT(DISTINCT r.redeemed_code_id),
      0
   ) AS totalRewards,
  COALESCE (
    COUNT(DISTINCT ocm.user_id),
    0
   ) AS totalStudents,
  COALESCE (sum(r.points), 0) AS totalPoints
  FROM
  `organisation_classrooms` `t`
   LEFT OUTER JOIN classroom_redeemed_codes r ON (
   r.classroom_id = t.classroom_id
   AND r.inactive = 0
   AND (
    r.date_redeemed >= 1393286400
    OR r.date_redeemed = 0
   )
   )
   LEFT OUTER JOIN organisation_classrooms_myusers ocm ON (
   ocm.classroom_id = t.classroom_id
   )
   WHERE
    t.organisation_id =37383
   GROUP BY title
   ORDER BY t.classroom_id ASC
   LIMIT 10

-- EDIT --

OOPS! I hate SQL sometimes... I have made a big mistake, I am trying to count the number of STUDENTS in the classroom_redeemed_codes rather than the organisation_classrooms_myuser table. I'm really sorry I should have picked that up sooner?!

classroom_id | totalUniqueStudents
     16             1
     17             2
     46             1
     51             1
     52             1

There are 7 rows in the classroom_redeemed_codes table but as classroom_id 46 has two rows although with the same myuser_id (this is the student id) this should appear as one unique student.

Does this make sense? Essentially trying to grab the number of unique students in the classroom_redeemed_codes tables based on the myuser_id column.

e.g a classroom id 46 could have 100 rows in the classroom_redeemed_codes tables, but if it is the same myuser_id for each this should show the totalUniqueStudents count as 1 and not 100.

Let me know if this isn't clear....

-- update -- I have the following query which seems to work borrowed from a user below which seems to work... (my head hurts) i'll accept the answer again. Sorry for the confusion - I think I was just over thinking this somewhat

select crc.classroom_id,
    COUNT(DISTINCT crc.myuser_id) AS users,
    COUNT( DISTINCT crc.redeemed_code_id ) AS classRewards,
    SUM( crc.points ) as classPoints, t.title
  from classroom_redeemed_codes crc
       JOIN organisation_classrooms t
         ON crc.classroom_id = t.classroom_id 
        AND t.organisation_id = 37383
        where crc.inactive = 0
        AND ( crc.date_redeemed >= 1393286400
        OR crc.date_redeemed = 0 )
        group by crc.classroom_id

Answer 1

I ran by first doing a pre-query aggregate of your points per specific class, then used left-join to it. I am getting more rows in the result set than your sample expected, but don't have MySQL to test/confirm directly. Howeverhere is a SQLFiddle of your query By doing your query with sum of points, and having a Cartesian result when applying the users table, it is probably the basis of duplicating the points. By pre-querying on the redeem codes itself, you just grab that value, then join to users.

SELECT
      t.classroom_id,
      title,
      COALESCE ( r.classRewards, 0 ) AS totalRewards,
      COALESCE ( r.classPoints, 0) AS totalPoints,
      COALESCE ( r.uniqStudents, 0 ) as totalUniqRedeemStudents,
      COALESCE ( COUNT(DISTINCT ocm.user_id), 0 ) AS totalStudents
   FROM
      organisation_classrooms t
         LEFT JOIN ( select crc.classroom_id,
                            COUNT( DISTINCT crc.redeemed_code_id ) AS classRewards,
                            COUNT( DISTINCT crc.myuser_id ) as uniqStudents,
                            SUM( crc.points ) as classPoints
                        from classroom_redeemed_codes crc
                           JOIN organisation_classrooms t
                              ON crc.classroom_id = t.classroom_id 
                              AND t.organisation_id = 37383
                        where crc.inactive = 0
                          AND ( crc.date_redeemed >= 1393286400
                           OR crc.date_redeemed = 0 )
                        group by crc.classroom_id ) r
            ON t.classroom_id = r.classroom_id

         LEFT OUTER JOIN organisation_classrooms_myusers ocm 
            ON t.classroom_id = ocm.classroom_id
   WHERE
      t.organisation_id = 37383
   GROUP BY 
      title
   ORDER BY 
      t.classroom_id ASC
   LIMIT 10

Answer 2

You need sum(r.points) and a subquery in the left outer join see below

 SELECT
 t.classroom_id,
 title,
 COALESCE (
   COUNT(DISTINCT r.redeemed_code_id),
     0
  ) AS totalRewards,
 COALESCE(sum(r.points),0) AS totalPoints
,COALESCE(sum(T1.cnt),0) as totalStudents
 FROM
  `organisation_classrooms` `t`
left outer join (select classroom_id, count(user_id) cnt
                  from organisation_classrooms_myusers 
 group by classroom_id) T1 on (T1.classroom_id=t.classroom_id)
  LEFT OUTER JOIN classroom_redeemed_codes r ON (
  r.classroom_id = t.classroom_id
  AND r.inactive = 0
  AND (
   r.date_redeemed >= 1393286400
   OR r.date_redeemed = 0
  )
  )
  WHERE
   t.organisation_id =37383
  GROUP BY title
  ORDER BY t.classroom_id ASC
  LIMIT 10