'How do I find the sum of playtime of all FPS games (mysql query)
I have 3 tables which i think is relevant although my guess is that you will probably need to utilize 2 out of 3 tables. So the thing here is I want to find the total playtime of all FPS games which is a genre of a videogame The first table is videogames which I don't think is relevant to solving this question but I will write it down anyways with some sample data so you get a better understanding
| videogameid (PK) | title | yearOfRelease |
|---|---|---|
| 114 | CSGO | 2012 |
| 115 | Starcraft | 2010 |
| 116 | Call Of Duty | 2008 |
Second table is workson. This the table that shows the developerid that works on the game as well as containing the genre attribute which is key to solving this question
| videogameid (FK) | developerid (FK) | genre |
|---|---|---|
| 114 | 23567 | FPS |
| 114 | 23568 | FPS |
| 114 | 23569 | FPS |
| 115 | 23443 | RTS |
| 116 | 23667 | FPS |
Third table is playtimes
| videogameid (FK) | playtime(hours) |
|---|---|
| 114 | 25,000,000 |
| 115 | 980,456 |
| 116 | 27,000,000 |
The expected outcome should be 52 million hours since the sum of playtime of all FPS games that exist in the dataset is 52 million
This is my attempt at the code:
SELECT p.videogameid, w.genre,SUM(p.playtime)
FROM workson AS w, playtimes AS p
WHERE p.videogameid = w.videogameid
AND genre = 'FPS';
However I got the wrong outcome where I only get the playtime of one fps game and not every single fps games that exist in the dataset. Am I missing something?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
