I have a database. These database has two tables.
One table is music.
| name | Date | Edition | Song_ID | Singer_ID |
|---|---|---|---|---|
| LA | 01.05.2009 | 1 | 1 | 1 |
| Second | 13.07.2009 | 1 | 2 | 2 |
| Mexico | 13.07.2009 | 1 | 3 | 1 |
| Let's go | 13.09.2009 | 1 | 4 | 3 |
| Hello | 18.09.2009 | 1 | 5 | (4,5) |
| Don't give up | 12.02.2010 | 2 | 6 | (5,6) |
| ZIC ZAC | 18.03.2010 | 2 | 7 | 7 |
| Blablabla | 14.04.2010 | 2 | 8 | 2 |
| Oh la la | 14.05.2011 | 3 | 9 | 4 |
| Food First | 14.05.2011 | 3 | 10 | 5 |
| La Vie est.. | 17.06.2011 | 3 | 11 | 8 |
| Jajajajajaja | 13.07.2011 | 3 | 12 | 9 |
And another table called singer
| Singer | nationality | Singer_ID |
|---|---|---|
| JT Watson | USA | 1 |
| Rafinha | Brazil | 2 |
| Juan Casa | Spain | 3 |
| Kidi | USA | 4 |
| Dede | USA | 5 |
| Briana | USA | 6 |
| Jay Ado | UK | 7 |
| Dani | Australia | 8 |
| Mike Rich | USA | 9 |
Now I would like to know which Edition has most American Singer involved. But the problem is, there is only the Singer_ID.
Is there a SQL Code to find that?
I gave that code, but got an error
%sql SELECT Singer_ID, COUNT(*) FROM music GROUP BY Singer_ID WHERE nationality == USA from singer
CodePudding user response：
You need to use joins when information is present in 2 different tables. here the join would be in SINGER_ID
SELECT EDITION, COUNT(DISTINCT SINGER.SINGER_ID)
FROM MUSIC
INNER JOIN SINGER ON MUSIC.SINGER_ID = SINGER.SINGER_ID
WHERE SINGER.NATIONALITY = 'USA'
GROUP BY EDITION
ORDER BY 2 DESC