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.

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.