SQL Count over multiple fields

First, I think you have a couple of mistakes in your problem description.

• In step 5, you describe Jane crediting John twice in video 2. I think you just got some columns mis-ordered in the values clause. It should be:

  insert into videos_videocredit (id, video_id, profile_id, position) values (7, 2, 1, 'writer');
  insert into videos_videocredit (id, video_id, profile_id, position) values (8, 2, 1, 'producer');
  

• Your results should show John credited in 2 videos, and Jane credited in 1 video.

  +------------+---------+--------------------+---------------+
  | full_name  | Uploads | Credited_by_others | Total_credits |
  +------------+---------+--------------------+---------------+
  | John Smith |       1 |                  1 |             2 | 
  | Jane Doe   |       1 |                  1 |             1 | 
  +------------+---------+--------------------+---------------+
  

I tested the following query on MySQL 5.1.57 and it gives the above result.

SELECT
  u.full_name,
  COUNT(DISTINCT myvideos.id) AS Uploads,
  COUNT(DISTINCT byothers.id) AS Credited_by_others,
  COUNT(DISTINCT credited.id) AS Total_credits
FROM userprofile_userprofile AS u
LEFT OUTER JOIN videos_video AS myvideos ON myvideos.uploaded_by_id = u.id
LEFT OUTER JOIN (
  videos_videocredit AS c USE INDEX (videocredit_profileid_videoid)
  INNER JOIN videos_video AS credited
    ON c.video_id = credited.id
) ON c.profile_id = u.id
LEFT OUTER JOIN videos_video AS byothers USE INDEX (video_up_id)
  ON c.video_id = byothers.id
  AND byothers.uploaded_by_id <> u.id
GROUP BY u.id

I created a couple of additional indexes and gave the query hints to use them.

CREATE INDEX video_up_id ON videos_video (id,uploaded_by_id);

CREATE INDEX videocredit_profileid_videoid ON videos_videocredit (profile_id,video_id);

This ensures that all tables (except userprofile) are accessed with the Using index mode, which means it can satisfy the query by reading only the index B-trees, without needing to read the table data. Here's the EXPLAIN report:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: u
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: myvideos
         type: ref
possible_keys: userprofile_video_e43a31e7
          key: userprofile_video_e43a31e7
      key_len: 4
          ref: test.u.id
         rows: 1
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: ref
possible_keys: videocredit_profileid_videoid
          key: videocredit_profileid_videoid
      key_len: 5
          ref: test.u.id
         rows: 1
        Extra: Using index
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: credited
         type: eq_ref
possible_keys: PRIMARY,video_up_id
          key: PRIMARY
      key_len: 4
          ref: test.c.video_id
         rows: 1
        Extra: Using index
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: byothers
         type: ref
possible_keys: video_up_id
          key: video_up_id
      key_len: 4
          ref: test.c.video_id
         rows: 1
        Extra: Using index
5 rows in set (0.00 sec)

Optimization can give variable reports when testing against a trivial number of rows. So we may see different results when testing against a real collection of data, and then it may become unnecessary to give the USE INDEX hints.

However, in spite of the solution above, I would hope do each task in a separate query. Doing everything in one query is complex to develop and test, and often costly for the RDBMS to execute. It will be even more complex if you need to add another count.

SELECT
  u.full_name,
  COUNT(DISTINCT myvideos.id) AS Uploads
FROM userprofile_userprofile AS u
LEFT OUTER JOIN videos_video AS myvideos ON myvideos.uploaded_by_id = u.id
GROUP BY u.id;

SELECT
  u.full_name,
  COUNT(DISTINCT byothers.id) AS Credited_by_others
FROM userprofile_userprofile AS u
LEFT OUTER JOIN videos_videocredit AS c
  USE INDEX (videocredit_profileid_videoid)
  ON c.profile_id = u.id
LEFT OUTER JOIN videos_video AS byothers
  USE INDEX (video_up_id)
  ON c.video_id = byothers.id AND byothers.uploaded_by_id <> u.id
GROUP BY u.id;

SELECT
  u.full_name,
  COUNT(DISTINCT credited.id) AS Total_credits
FROM userprofile_userprofile AS u
LEFT OUTER JOIN (
  videos_videocredit AS c
  USE INDEX (videocredit_profileid_videoid)
  INNER JOIN videos_video AS credited
    ON c.video_id = credited.id
) ON c.profile_id = u.id
GROUP BY u.id;

The total credit is just the sum as the credit-by-upload and the foreign credit. Since credit-by-upload is easy, here is just the foreign credit. Hold your breath for two-fold subquery.

SELECT profile_id, COUNT(video_id) AS foreign_credit
       FROM (SELECT DISTINCT profile_id, video_id FROM videos_videocredit
             WHERE (profile_id, video_id) NOT IN (SELECT uploaded_by_id, id FROM videos_video)) AS crsq
GROUP BY profile_id;

This becomes more palpable with a view. We make a view that selects only pairs (profile_id, video_id) of people credited in videos which they did not upload themselves. Let's call the view vfcredits.

CREATE VIEW vfcredits AS
  SELECT DISTINCT profile_id, video_id FROM videos_credit
  WHERE (profile_id, video_id) NOT IN (SELECT uploaded_by_id, id FROM videos_video);

Now we can happily paste this into the main query that aggregates foreign credits:

SELECT profile_id, COUNT(video_id) AS foreign_credit
FROM vfcredits
GROUP BY profile_id;

Now let's put it all together. We make two more views, one to count own credits and one to count foreign credits:

CREATE VIEW vowncount AS
  SELECT uploaded_by_id AS profile_id, COUNT(*) AS own_credits
  FROM videos_video
  GROUP BY uploaded_by_id;

CREATE VIEW vforeigncount AS
  SELECT profile_id, COUNT(video_id) AS foreign_credits
  FROM vfcredits
  GROUP BY profile_id;

Finally, the complete selection:

SELECT name,
       own_credits,
       foreign_credits,
       own_credits + foreign_credits AS total_credits
FROM userprofile_userprofile
JOIN vowncount ON(userprofile_userprofile.id = vowncount.profile_id)
JOIN vforeigncount ON(userprofile_userprofile.id = vforeigncount.profile_id);