MySQL trying to count from two columns

Question

I’m new at this so I’m having a hard time solving this problem, I’ve searched high and low but I can’t seem to find an answer.

This is how my tables look:

Table artist

Columns	Constraints
Artist_id	PK
Name	-

Table song

Columns	Constraints
Song_id	PK
Title	-
artist_id	FK
co_artist_id	FK

Table top_list

Columns	Constraints
Id	PK FK

I’ve made two foreign keys in the song table to the same primary key (artist_id). And also made a foreign key in top_list with song_id

My query is:

SELECT a.name Artist, COUNT(*) 
FROM top_list tl
INNER JOIN song s ON tl.id = s.song_id
INNER JOIN artist a ON s.artist_id = a.artist_id
GROUP BY a.name
ORDER BY COUNT(*) DESC;
LIMIT 1;

I want to count both the artist and the co-artist. ATM it only counts song.artist_id and not song.co_artist_id

user_15328282 · Answer

Firstly count the artist, then count co_artists. After that you can sum them.

This is 'artists' table:

And this is 'songs' table:

And the 'top_lists' table:

And the query is:

SELECT artist AS Artist, SUM(amount) AS Amount
FROM (
    SELECT artist_id AS artist, COUNT(id) AS amount
    FROM songs
    WHERE id IN (SELECT id FROM top_lists)
    GROUP BY artist_id
    UNION
    SELECT co_artist_id, COUNT(id)
    FROM songs
    WHERE id IN (SELECT id FROM top_lists)
    GROUP BY co_artist_id
) AS inner_table
GROUP BY artist
ORDER BY Amount DESC;

The result is:

if you need you can do inner join to get the name of artists

And this is final query:

SELECT artists.name AS Artist, SUM(amount) AS Amount
FROM (
    SELECT artist_id AS artist, COUNT(id) AS amount
    FROM songs
    WHERE id IN (SELECT id FROM top_lists)
    GROUP BY artist_id
    UNION
    SELECT co_artist_id, COUNT(id)
    FROM songs
    WHERE id IN (SELECT id FROM top_lists)
    GROUP BY co_artist_id
) AS inner_table
INNER JOIN artists
ON artists.id = inner_table.artist
GROUP BY artist
ORDER BY Amount DESC;

And the result is:

MySQL trying to count from two columns

1 Answers