Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    8

    Unanswered: Multiple COUNT()s in Query

    I thought that this would be rather simplistic and straight forward, however, I am having a hard time getting the desired results.

    I have a query as such:

    SELECT
    P.Planet_Name,
    COUNT(PM.Planet_Moon_Key) AS Moon_Count
    COUNT(PS.Planet_Star_Key) AS Star_Count
    FROM dbo.Planet AS P
    LEFT OUTER JOIN dbo.Planet_Moon AS PM
    ON PM.Planet_Key = P.Planet_Key
    LEFT OUTER JOIN dbo.Planet_Star AS PS
    ON PS.Planet_Key = P.Planet_Key
    WHERE P.Planet_Name LIKE @Planet_Name + '%'

    I am trying to get the name of the planet, as well as a count of how many stars and moons that are associated with the planet. Each moon is a record, each star is a record.

    My Moon count seems to be correct, though the Star count is the same as my Moon count for some reason.

    PS: I tried to make this as realistic as possible, so hopefully the Planet/Moon/Star setup makes it easier

    PSS: I'm trying to avoid DISTINCT and subqueries.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Erwina View Post
    PSS: I'm trying to avoid DISTINCT and subqueries.
    why??

    if you really need to avoid subqueries (heavens knows why -- no pun intended!!), then the best you can do is two separate queries, one that counts the moons and one that counts the stars
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Two issues.

    Did you realy executed the query?

    (1) You didn't use GROUP BY clause.
    So, It might not be able to co-exist "P.Planet_Name" and "COUNT(PM.Planet_Moon_Key)" in a select-list.

    (2) If a Planet has both of Moons and Stars,
    Both of COUNT(PM.Planet_Moon_Key) and COUNT(PS.Planet_Star_Key) might be
    COUNT(DISTINCT PM.Planet_Moon_Key) * COUNT(DISTINCT PS.Planet_Star_Key)

  4. #4
    Join Date
    Jan 2012
    Posts
    8
    Unless it's not possible by any other means, I tend to avoid usage of DISTINCT as there's generally a way to limit the result set to that of what you actually.

    In most cases, a subquery can be replaced by a LEFT JOIN, and in fewer cases in which a LEFT JOIN will not solve the issue, you can toss in a CROSS/OUTER APPLY.

    It's just a matter of trying to avoid, but again, if there's no other solution then it's what has to be done, and why I'm asking as nothing that I see as "optimal" or the best way to do it, immediately comes to mind.

    @Tonkuma, no I did not run the query, I was just trying to make up a descriptive example without generic tableA.id = tableB examples.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if a single planet has 4 moons and 5 stars, then when you join the planet to its moons and also to its stars, the double join produces 20 intermediate rows

    that's why your counts are the same -- or would be, if you had included a GROUP BY clause

    and when you use a GROUP BY clause, DISTINCT is (usually) not necessary
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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