SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2008
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Also list all the sub_name_id's

    Hi,

    I would like to know how to show all the "sub_name_id" values, but it only shows the sub_name_id when there are record counts. for table1.sub_name_id


    Code SQL:
     
    SELECT DISTINCT Sub_Name, department_2_ID
    ,table1.count1
    ,COALESCE(table1.count1,0) AS totalCount
     
    ,table1.sub_name_id
     
    FROM subtype
    LEFT OUTER
     
    JOIN ( SELECT sub_name_id
           , COUNT(*) AS count1
           FROM Listings
     
           GROUP BY sub_name_id
         ) AS table1
     
    ON table1.sub_name_id = subtype.sub_name_id
     
    WHERE department_2_id = 0527
    AND department_id = 09

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Select subtype.sub_name_id instead of table1.sub_name_id.

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2008
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, guido2004.
    that works fine buy replacing "subtype.sub_name_id instead of table1.sub_name_id".
    Last edited by tilly2; Jan 28, 2009 at 05:35.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Sorry, I don't understand. You are already counting the number of occurrances of each Sub_type_id in Listings.
    Could you give an example of the data you have, and the result you want?

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2008
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Quote Originally Posted by guido2004 View Post
    Sorry, I don't understand. You are already counting the number of occurrances of each Sub_type_id in Listings.
    Could you give an example of the data you have, and the result you want?
    Sorry guido2004, for waisting your time.
    my mistake i had a wrong value stored. thats why i posted my previous message. [but deleted it now]. it all working as expected.



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
  •