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

    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0

    Count only one field of a query.


    I can say it...I need help.

    We use HP Quality Center here at work and the dev team is really protective to the point where they will not even allow my to make an inline table. What I have is a query that checks for open defects in our DB but in order to get it to do it properly I need to have many fields in the select statement. My manager wants a count of the defects but because of all the fields, count treats each entry in the return as unique so I end up with every row having a 1 in the count.

    Here is the query:

    SELECT DISTINCT

    count (BUG.BG_BUG_ID) as #, BUG.BG_BUG_ID AS Defect_Id, BUG.BG_DETECTION_VERSION AS Detected_in_Version, BUG.BG_PROJECT AS Project, BUG.BG_STATUS AS Status,
    BUG.BG_DETECTED_BY AS Detected_by, LINK.LN_ENTITY_TYPE AS Link_Type, TEST.TS_TEST_ID AS Test_ID10, TEST.TS_NAME AS Test_Name

    FROM
    td.BUG AS BUG
    LEFT OUTER JOIN
    td.LINK AS LINK ON BUG.BG_BUG_ID = LINK.LN_BUG_ID
    FULL outer JOIN
    td.STEP AS STEP
    INNER JOIN
    td.TEST AS TEST ON STEP.ST_TEST_ID = TEST.TS_TEST_ID ON LINK.LN_ENTITY_ID = STEP.ST_ID
    full outer join
    td.TESTCYCL ON td.TESTCYCL.TC_TESTCYCL_ID = LINK.LN_ENTITY_ID

    WHERE
    (BUG.BG_PROJECT IN ('EWC', 'SCBP WC'))
    AND
    (BUG.BG_STATUS NOT IN ('Closed', 'Duplicate', 'Rejected', 'Withdrawn'))
    AND
    (LINK.LN_ENTITY_TYPE ='STEP' or LINK.LN_ENTITY_TYPE is NULL or LINK.LN_ENTITY_TYPE = 'TEST' or LINK.LN_ENTITY_TYPE = 'TESTCYCL')

    group by BUG.BG_BUG_ID

    order by BUG.BG_BUG_ID

    What I need is a count of just bug.bg_bg_id to be placed in the first field

    MOCKED UP Results: (This is what I need it to do)

    # of occurances Defect_Id Detected_in_Version Project Status Detected_by Link_Type Test_ID10 Test_Name
    2 5757 201204 SCBP WC Deferred STEP 68211 States&Mod_ARAP_Mod_NB_Edit_msg_TC004
    2 5757 201204 SCBP WC Deferred STEP 68212 States&Mod_ARAP_Mod_NB_Edit_msg_TC005
    2 5759 201204 SCBP WC Deferred STEP 68211 States&Mod_ARAP_Mod_NB_Edit_msg_TC004
    2 5759 201204 SCBP WC Deferred STEP 68212 States&Mod_ARAP_Mod_NB_Edit_msg_TC005

    This is what it does:

    # of occurances Defect_Id Detected_in_Version Project Status Detected_by Link_Type Test_ID10 Test_Name
    1 5757 201204 SCBP WC Deferred STEP 68211 States&Mod_ARAP_Mod_NB_Edit_msg_TC004
    1 5757 201204 SCBP WC Deferred STEP 68212 States&Mod_ARAP_Mod_NB_Edit_msg_TC005
    1 5759 201204 SCBP WC Deferred STEP 68211 States&Mod_ARAP_Mod_NB_Edit_msg_TC004
    1 5759 201204 SCBP WC Deferred STEP 68212 States&Mod_ARAP_Mod_NB_Edit_msg_TC005

    Any help would be greatly appreciated.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,130
    Rep Power
    4274
    Originally Posted by QA_Keith
    ... count treats each entry in the return as unique so I end up with every row having a 1 in the count.
    no, COUNT works just fine -- it's your GROUP BY that's creating the results of 1

    every BUG.BG_BUG_ID is unique, correct? so you're asking it to count how many values each unique id has, and that's always gonna be equal to 1

    besides, how come you're trying to count them at the same time as printing them out?

    if you're gonna print them out, use your application language (vb or asp or whatever) to count them as you're printing them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0
    Originally Posted by r937
    no, COUNT works just fine -- it's your GROUP BY that's creating the results of 1

    every BUG.BG_BUG_ID is unique, correct? so you're asking it to count how many values each unique id has, and that's always gonna be equal to 1

    besides, how come you're trying to count them at the same time as printing them out?

    if you're gonna print them out, use your application language (vb or asp or whatever) to count them as you're printing them
    Bug IDs are not unique. One Bug ID can be holding up multiple test cases so a Bug ID can be in multiple rows.

    Also, I am not printing them out. The examples given are how they appear on the query return.

IMN logo majestic logo threadwatch logo seochat tools logo