MySQL subquery JOIN

Question

Trying to put together a single SQL query that will grab COUNT values from one table, and an AVG of a SUM from another. The two queries work separately, but I can't figure out how to properly JOIN them into one.

The two queries -- the first having been shortened to two sums:

SELECT SUM(CASE WHEN `eventClass`='GOAL' AND `player1ID`=001 THEN 1 ELSE 0 END) goals,
SUM(CASE WHEN `eventClass`='GOAL' AND `player2ID`=001 THEN 1 ELSE 0 END) assists
FROM `plays`

&

SELECT ROUND(AVG(`toi`)) AS avgTime 
FROM ( 
    SELECT SUM(TIME_TO_SEC(`shiftDuration`)) AS toi 
    FROM `shifts` 
    WHERE `playerID`=001 
    GROUP BY `gameNo`
) t;

Now I've tried a few things for the join, but am not sure on what to join them, as I in the first query I am looking for my search value 001 in one of two columns, depending on the row.

Much obliged to any help!

Andrew

---

SAMPLE DATA:

table `plays`:
+--------+--------+------------+-----------+-----------+
| playNo | gameNo | eventClass | player1ID | player2ID |
+--------+--------+------------+-----------+-----------+
|      1 |      1 |   GOAL     |   001     |    002    |
|    210 |      3 |   GOAL     |   003     |    001    |
+--------+--------+------------+-----------+-----------+

table `shifts`:
+---------+--------+----------+---------------+
| shiftNo | gameNo | playerID | shiftDuration |
+---------+--------+----------+---------------+
|       1 |      1 |   001    |      65       |
|       2 |      1 |   001    |      38       |
|      12 |      1 |   002    |      47       |
|      22 |      3 |   001    |      13       |
+------=--+--------+----------+---------------+

Now I want to take the sum of all player 001 shifts in a game, for the game total; it is the average of all these game totals for 001 that I'd like to output.

Desired output -- goals and assists are all-game TOTALS for 001, whereas avgTime is a per-game AVERAGE. Here we'll mark it in seconds.

+-------+-------+---------+---------+
| games | goals | assists | avgTime |
+-------+-------+---------+---------+
|   3   |  2    |   1     |   800   |
+-------+-------+---------+---------+

Answer 1

This is not as compact as @GordonLinoff but does handle more than one player at a time, and breaks down by individual game:

SELECT R.gameNo, R.playerID, R.goals, R.assists, S.toi FROM (
  SELECT P1.gameNo, P1.playerID, P1.goals, P2.assists FROM (
    SELECT
      gameNo
      , player1ID playerID
      , COUNT(playNo) goals
    FROM Plays
    WHERE eventClass = 'GOAL'
    GROUP BY gameNo, player1ID
  ) P1
  LEFT JOIN (
    SELECT
      gameNo
      , player2ID playerID
      , COUNT(playNo) assists
    FROM Plays
    WHERE eventClass = 'GOAL'
    GROUP BY gameNo, player2ID
  ) P2 ON P1.gameNo = P2.gameNo AND P1.playerID = P2.playerID
  UNION
  SELECT P2.gameNo, P2.playerID, P1.goals, P2.assists FROM (
    SELECT
      gameNo
      , player1ID playerID
      , COUNT(playNo) goals
    FROM Plays
    WHERE eventClass = 'GOAL'
    GROUP BY gameNo, player1ID
  ) P1
  RIGHT JOIN (
    SELECT
      gameNo
      , player2ID playerID
      , COUNT(playNo) assists
    FROM Plays
    WHERE eventClass = 'GOAL'
    GROUP BY gameNo, player2ID
  ) P2 ON P1.gameNo = P2.gameNo AND P1.playerID = P2.playerID
) R
JOIN (
  SELECT
    gameNo
    , playerID
    , SUM(shiftDuration) AS toi 
  FROM Shifts
  GROUP BY gameNo, playerID
) S
ON R.gameNo = S.gameNo AND R.playerID = S. playerID
ORDER BY gameNo, playerID
;

SQL Fiddle

I dropped TIME_TO_SEC (which to me doesn't seem to have any effect - based on the given data), and did not AVG in order to return the time played in the respective game.

But apparently, you wanted overall totals (and thus AVG). So - this should be the answer to your question:

SELECT R.playerID, R.goals, R.assists, S.avgTime FROM (
  SELECT P1.playerID, P1.goals, P2.assists FROM (
    SELECT
      player1ID playerID
      , COUNT(playNo) goals
    FROM Plays
    WHERE eventClass = 'GOAL'
    GROUP BY player1ID
  ) P1
  LEFT JOIN (
    SELECT
      player2ID playerID
      , COUNT(playNo) assists
    FROM Plays
    WHERE eventClass = 'GOAL'
    GROUP BY player2ID
  ) P2 ON P1.playerID = P2.playerID
  UNION
  SELECT P2.playerID, P1.goals, P2.assists FROM (
    SELECT
      player1ID playerID
      , COUNT(playNo) goals
    FROM Plays
    WHERE eventClass = 'GOAL'
    GROUP BY gameNo, player1ID
  ) P1
  RIGHT JOIN (
    SELECT
      player2ID playerID
      , COUNT(playNo) assists
    FROM Plays
    WHERE eventClass = 'GOAL'
    GROUP BY player2ID
  ) P2 ON P1.playerID = P2.playerID
) R
JOIN (
    SELECT S.playerID, ROUND(AVG(S.toi)) avgTime
    FROM (
      SELECT
        gameNo
        , playerID
        , SUM(shiftDuration) AS toi 
      FROM Shifts
      GROUP BY gameNo, playerID
    ) S
    GROUP BY S.playerID
) S
ON R.playerID = S. playerID
ORDER BY R.playerID
;

Updated SQL Fiddle

The fiddles start with the individual parts of the above statements, and advance to the complete assembly.

Answer 2

Have you tried using subqueries or cross join?

SELECT p.goals, p.assist, s.avgTime
FROM (SELECT SUM(CASE WHEN `eventClass`='GOAL' AND `player1ID`=001 THEN 1 ELSE 0 END) goals,
             SUM(CASE WHEN `eventClass`='GOAL' AND `player2ID`=001 THEN 1 ELSE 0 END) assists
      FROM `plays`
     ) p CROSS JOIN
     (SELECT ROUND(AVG(`toi`)) AS avgTime 
      FROM (SELECT SUM(TIME_TO_SEC(`shiftDuration`)) AS toi 
            FROM `shifts` 
            WHERE `playerID`=001 
            GROUP BY `gameNo`
           ) s
     ) s;

It is not clear to me where the games column is coming from.

Don't attempt to do this with just joins and a single aggregation. You are summarizing along two different dimensions -- and doing joins before aggregations tends to cause unwanted cartesian products.