Using COUNT in Self Join

Databases
1

I’m trying to retrieve total counts for musical band/artist type from a single table called ‘artist’.

For the sake of simplification, let’s say the table has only two cols a VARCHAR ‘artist_id’ and an ENUM ‘type’.

My problem is, there will never be any matching criteria for which to JOIN upon.

Here’s my code which is bringing back empty counts for both:


select count(band.artist_id) as bands, count(combo.artist_id) as combos 
from artist as band
right outer join artist as combo on combo.artist_id = band.artist_id and combo.type = 'combination'
where band.type = 'band'
2

After a few more minutes of thinking about the problem, I guess there’s no way to accomplish this without a subquery. This is what I resorted to:


select count(artist_id) as bands, (select count(artist_id) from artist where type='combination') as combos 
from artist
where type = 'band'
3

I’m a little confused on what you want your end result to be. I don’t think you even need a join but can you supply sample data and what you want the result to be?

4 

select sum(case when type = 'band' then 1 else 0 end) as bands,
       sum(case when type = 'combination' then 1 else 0 end) as combos
  from artist
 where type in ('band','combination')