SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2012
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:

    Code:
    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. #2
    SitePoint Enthusiast
    Join Date
    Jun 2012
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

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

  3. #3
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    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. #4
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    696
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    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')


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •