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?
CodePudding user response：
Not sure how you got the query to run without a groupby.
First only keep the genre in the select other than the aggregation column and the same non-aggregation column should be in GROUP BY. Use a JOIN instead of an equi-join, but it's ones preference.
The query should look something like this.
SELECT w.genre,SUM(p.playtime)
FROM workson AS w
JOIN playtimes AS p
on p.videogameid = w.videogameid
WHERE w.genre = 'FPS'
GROUP BY w.genre;