How do I find the title of a videogame with the maximum amount of developers working on it ? (SQL qu-CodePudding

Let's say that we have 3 tables which are videogames, developers, workson.

Under the videogames table, we have attributes such as

videogameid (PK)
title
year
genre

Then for developers we have

developerid (PK)
name
gender

Then for the workson table

videogameid(PK & FK )
developerid (PK & FK )

My attempt at the code:

SELECT MAX(videogameid)
FROM 
(SELECT videogameid
FROM workson
GROUP BY videogameid
HAVING COUNT(DISTINCT developerid)>5
)videogames_with_most_developers;

However, I have failed to retrieve the answer with the title ( mainly because I did not select the title) but that's because I can't seem to make the connection.

EDIT: So we have some sample data where for table videogames we have INSERT INTO videogames (videogameid, title,year,genre)
VALUES (111,World of Warcraft ,2004, MMORPG); INSERT INTO videogames (videogameid, title,year,genre) VALUES (112,Starcraft 2 ,2008, RTS);

For the table developers we have INSERT INTO developers( developerid, gender, name) VALUES ( 98734, M, Johnson); INSERT INTO developers( developerid, gender, name) VALUES ( 98735, F, Regina); INSERT INTO developers( developerid, gender, name) VALUES (98736, M , Lamar);

For the table workson INSERT INTO (videogameid, developerid) VALUES (111, 98734); INSERT INTO (videogameid, developerid) VALUES (111,98735); INSERT INTO (videogameid, developerid) Values(112,98736);

The expected output should be the title 'World of Warcraft' because it has the maximum number of directors working on it which is 2 whereas title such as 'Starcraft' does not have the maximum number of developers working on it as seen in this sample data

CodePudding user response：

Which columns/aggregate function(MAX,COUNT,AVG etc.) you select in the select query will be shown as a table. In your query:

 SELECT MAX(videogameid) FROM ....

only the id of the video game with the largest value will be displayed. You just select the videogameid with the greatest value.In the second part of the query select videogameid that work more than 5 developers.Again there is no title selected to connect to the outer sql query.

Modified version of your query:

SELECT videogameid,title
FROM videogames WHERE videogameid IN
(SELECT videogameid
FROM workson
GROUP BY videogameid
HAVING COUNT(DISTINCT developerid)>5
);

This query shows videogameid and title with more than 5 developers

Another query:

SELECT COUNT(developerid) AS dev_count,videogameid FROM workson GROUP BY videogameid
ORDER BY dev_count DESC LIMIT 1;

this shows videogameid and number of developers that work on selected video game with the maximum amount of developers working on it.There is no title.

If we want to see the title :

SELECT videogameid,title FROM videogames WHERE videogameid IN
(SELECT videogameid FROM (SELECT COUNT(developerid) AS count,videogameid FROM workson GROUP BY videogameid
ORDER BY COUNT(developerid) DESC LIMIT 1) AS T);

This query shows title and videogameid with the maximum amount of developers working on it.