I am relatively new to using group by and aggregate functions in SQL, I have the following tables:
CREATE TABLE `artists` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR( 100 ) NOT NULL );
CREATE TABLE `genres` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR( 100 ) NOT NULL);
CREATE TABLE `songs` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR( 100 ) NOT NULL, `artist_id` INT NOT NULL );
CREATE TABLE `songs_genres` (`song_id` INT NOT NULL, `genre_id` INT NOT NULL );
I am looking to return artists that have songs in multiple genres. Any ideas most welcome!
I have this so far to link everything together but cant quite work out the grouping / aggregation required:
select a.name as name, g.name as genre
from artists a inner join songs s on a.id = s.artist_id
inner join songs_genres sg on s.id = sg.song_id
inner join genres g on g.id = sg.genre_id
Thanks in advance.
This might help. It will find how many genres have multiple artists:
SELECT COUNT(a.Name) AS "No. of Artists",
g.Name AS "Genre"
FROM Artists a
INNER JOIN songs s on a.id = s.artist_id
INNER JOIN songs_genres sg on s.id = sg.song_id
INNER JOIN genres g on g.id= sg.genre_id
GROUP BY g.Name
This will give you the number of genres per artist:
SELECT COUNT(g.Name) AS "No. of Genres",
a.Name AS "Artist"
FROM Artists a
INNER JOIN songs s on a.id = s.artist_id
INNER JOIN songs_genres sg on s.id = sg.song_id
INNER JOIN genres g on g.id= sg.genre_id
GROUP BY a.Name
Adding a HAVING clause will allow you to narrow your results to those Artists/Genres with more than any number:
SELECT COUNT(g.Name) AS "No. of Genres",
a.Name AS "Artist"
FROM Artists a
INNER JOIN songs s on a.id = s.artist_id
INNER JOIN songs_genres sg on s.id = sg.song_id
INNER JOIN genres g on g.id= sg.genre_id
GROUP BY a.Name
HAVING Count(g.Name) > 1 -- or 2, or 10. This will skip over single-genre artists
What you're doing in these queries is counting how many times a given value shows up when grouping by another value.