MySQL trying to count from two columns-CodePudding

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

Artist_id PK

Name

Table song

Song_id PK

Title

artist_id FK

co_artist_id FK

Table top_list

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

CodePudding user response：

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: