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 |
+-------+-------+---------+---------+
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments