'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