Using COUNT in Self Join
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'
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
where type = 'band'
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?
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
where type in ('band','combination')