#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0

    COUNT in derived table not working


    Hello,

    Me again. I've been working on the "Hard" problems here:
    sqlzoo.net/a2d.htm
    Based on the database explained here:
    sqlzoo.net/a2.htm

    I am working on Question 12, which states:
    Create a list showing for EVERY musician born in Britain the number of compositions and the number of instruments played.

    I can get it to show every musician from England or Scotland (no records from Wales). Additionally, my count statements work if executed independently - that is, I can calculate the number of compositions per artist and the number of instruments played.

    When I put it all together however, I am getting the same number all the way down the table for both columns (compositions and instruments).

    Here is my code:
    Code:
    SELECT musician.m_name, place_country, a1.numcomps, a2.numins
    FROM musician JOIN place
    JOIN
    (SELECT COUNT(cmpn_no) as numcomps
    FROM has_composed
    GROUP BY cmpr_no) as a1
    JOIN
    (SELECT COUNT(instrument) as numins
    FROM performer
    GROUP BY perf_is) as a2
    ON born_in = place_no
    GROUP BY m_no
    HAVING born_in IN 
    (SELECT place_no FROM place WHERE place_country IN ('Scotland', 'England'));
    My suspicion is that I am executing the JOINS incorrectly, any tips or ideas?

    Thanks! All help is greatly appreciated.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    Each join clause should have an on clause. You ougth to get a syntax error when the query is compiled.

    You have three join clauses but only one on clause. The group by and the having clause in the outmost query is not needed. You can put the restriction on place_country in a where clause since you are joining on this table already.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0
    Hello,

    Thank you for your reply. I had a WHERE clause initially, but when I run that it gives me an infinite number of results, where each name is repeated 100 times and the other columns are cycled, that's why I grouped by musician id.

    How should I be joining the tables? I thought the derived tables returned only one value, so how can I join them to the rest of the results?

    Thanks!
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    Since you have group by clauses in your derived tables, they will return multiple rows.

    Code:
    (SELECT COUNT(cmpn_no) as numcomps
    FROM has_composed
    GROUP BY cmpr_no) as a1
    In this case you should include the cmpr_no in the select list and use that column when joining the a1 table with musician.m_no. Likewise for the a2 table.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0
    I think I'm just getting myself more confused, this is what I have now:

    Code:
    SELECT m_no,musician.m_name, born_in, place_country, a1.numcomps, a2.numins, cmpr_no, perf_is
    FROM musician JOIN place JOIN composer JOIN performer JOIN has_composed
    JOIN
    (SELECT COUNT(cmpn_no) as numcomps
    FROM has_composed
    GROUP BY cmpr_no) as a1
    JOIN
    (SELECT COUNT(instrument) as numins
    FROM performer
    GROUP BY perf_is) as a2
    ON born_in = place_no AND has_composed.cmpr_no = composer.comp_no AND perf_is = m_no
    WHERE born_in IN (SELECT place_no FROM place WHERE place_country IN ('Scotland', 'England'));
    It doesn't return an error but the result isn't right. Am I not joining correctly?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by deathagent12
    Am I not joining correctly?
    that's right, you aren't

    first, as a rule of thumb, if your query involves more than one table, you should always qualify every single column in the query with its table name

    as for the joins, the ON clauses go right after the JOINs
    Code:
    SELECT musician.m_no
         , musician.m_name
         , place.place_country
         , COALESCE(a1.numcomps,0) AS numcomps
         , COALESCE(a2.numins,0) AS numins
      FROM musician 
    INNER
      JOIN place 
        ON place.place_no = musician.born_in
       AND place_country IN ('Scotland', 'England')
    LEFT OUTER
      JOIN ( SELECT cmpr_no
                  , COUNT(*) as numcomps
               FROM has_composed
             GROUP 
                 BY cmpr_no ) as a1
        ON a1.cmpr_no = composer.comp_no       
    LEFT OUTER
      JOIN ( SELECT perf_is
                  , COUNT(*) as numins
               FROM performer
             GROUP 
                 BY perf_is ) as a2
        ON a2.perf_is = musician.m_no
    i have a feeling that the joins to the subqueries should be LEFT OUTER JOINs

    Edit: removed joins to composer and WHERE clause subquery to place

    Comments on this post

    • deathagent12 agrees : Awesome!
    Last edited by r937; July 5th, 2012 at 10:15 PM. Reason: removed join to composer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0
    Okay, it didn't work as written because it didn't recognize the composer id field so I added a JOIN after musician, leading to this:

    Code:
    SELECT musician.m_no
         , musician.m_name
         , place.place_country
         , COALESCE(a1.numcomps,0) AS numcomps
         , COALESCE(a2.numins,0) AS numins
      FROM musician JOIN composer
          ON composer.comp_is = musician.m_no
    INNER
      JOIN place 
        ON place.place_no = musician.born_in
       AND place_country IN ('Scotland', 'England')
    LEFT OUTER
      JOIN ( SELECT cmpr_no
                  , COUNT(*) as numcomps
               FROM has_composed
             GROUP 
                 BY cmpr_no ) as a1
        ON a1.cmpr_no = composer.comp_no       
    LEFT OUTER
      JOIN ( SELECT perf_is
                  , COUNT(*) as numins
               FROM performer
             GROUP 
                 BY perf_is ) as a2
        ON a2.perf_is = musician.m_no;
    Thank you for your help (again). Just a general question, the left join is to return composers from Britain even if they play zero instruments or have no compositions right?

    Thanks!
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by deathagent12
    ... the left join is to return composers from Britain even if they play zero instruments or have no compositions right?
    correct
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo