Select sum if and having count

I have 3 tables table artist with artist_id, name;

table album with album_id, artist_id, name;

table songs with song_id, artist_id, album_id, (few more things but I don’t think they are necessary for this problem);

I want to select a table with the name of the artist, number of albums with equal or more than 10 songs, number or albums with less than 10 songs.

To count the number of songs on each album its this:

select album.name, count(DISTINCT songs.song_id)
from album inner join faixas on album.album_id = songs.album
group by album.name

now I am aware I will have to have to make sum if like this somewhere and make more than 1 select just not seeing how to connect both

sum(if(count (song_id)>=10,1,0)) and sum(if(count (song_id)<10,1,0))

Answer

I think you need two levels of aggregation, with conditional aggregation on the outer level:

select a.artist_id, a.name,
       sum(num_songs >= 10) as cnt_10_plus,
       sum(num_songs < 10) as cnt_9_minus,
from artists a join
     album al
     on al.artist_id = a.artist_id join
     (select s.album_id, count(*) as num_songs
      from songs s
      group by s.album_id
     ) s
     on al.album_id = s.album_id
group by a.artist_id, a.name;