Row Count with subquery?

Question

Row Count with subquery?

Old Hand

Points: 377

November 4, 2002 at 1:29 pm

I have a need to see how many "Samples" belong to all "Customer Service Requests" that belong to "Test Group 3". I want to do this with a single query but can't find a way to do so. I currently do this by first creating a view, then querying the view.

Here is the SQL for the view:

 

SELECT DISTINCT TOP 100 PERCENT dbo.CUST_SVS_RQST.SERV_RQST_ID, dbo.TEST_EVENT_SAMPLE.SAMPLE_ID

FROM dbo.CUST_SVS_RQST INNER JOIN

                      dbo.TEST_EVENT ON dbo.CUST_SVS_RQST.SERV_RQST_ID = dbo.TEST_EVENT.SERV_RQST_ID INNER JOIN

                      dbo.TEST_EVENT_SAMPLE ON dbo.TEST_EVENT.TST_EVENT_ID = dbo.TEST_EVENT_SAMPLE.TST_EVENT_ID

GROUP BY dbo.CUST_SVS_RQST.SERV_RQST_ID, dbo.TEST_EVENT.TST_EVENT_ID, dbo.TEST_EVENT_SAMPLE.SAMPLE_ID, 

                      dbo.TEST_EVENT.TST_GRP_ID

HAVING      (dbo.TEST_EVENT.TST_GRP_ID = 3)

ORDER BY dbo.CUST_SVS_RQST.SERV_RQST_ID

Here is the SQL used to then query the view:

 

SELECT SERV_RQST_ID, COUNT(SERV_RQST_ID) AS SAMPLE_CNT

FROM  dbo.IMCL_SAMPLE

GROUP BY SERV_RQST_ID

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 1

How about:

SELECT IMCL_SAMPLE.SERV_RQST_ID, COUNT(IMCL_SAMPLE.SERV_RQST_ID) AS SAMPLE_CNT

FROM

(SELECT DISTINCT Top 100 percent dbo.CUST_SVS_RQST.SERV_RQST_ID,

dbo.TEST_EVENT_SAMPLE.SAMPLE_ID

FROM dbo.CUST_SVS_RQST

INNER Join dbo.TEST_EVENT

ON dbo.CUST_SVS_RQST.SERV_RQST_ID = dbo.TEST_EVENT.SERV_RQST_ID

INNER JOIN dbo.TEST_EVENT_SAMPLE

ON dbo.TEST_EVENT.TST_EVENT_ID = dbo.TEST_EVENT_SAMPLE.TST_EVENT_ID

GROUP BY dbo.CUST_SVS_RQST.SERV_RQST_ID,

dbo.TEST_EVENT.TST_EVENT_ID,

dbo.TEST_EVENT_SAMPLE.SAMPLE_ID,

dbo.TEST_EVENT.TST_GRP_ID

HAVING (dbo.TEST_EVENT.TST_GRP_ID = 3)) AS IMCL_SAMPLE

GROUP BY IMCL_SAMPLE.SERV_RQST_ID

Edited by - Scorpion_66 on 11/04/2002 2:11:27 PM

garylzon Old Hand Points: 377 More actions · Answer 2

Thanks Scorpion_66, this query worked ...

quote:
How about:
SELECT IMCL_SAMPLE.SERV_RQST_ID, COUNT(IMCL_SAMPLE.SERV_RQST_ID) AS SAMPLE_CNT
FROM
(SELECT DISTINCT Top 100 percent dbo.CUST_SVS_RQST.SERV_RQST_ID,
dbo.TEST_EVENT_SAMPLE.SAMPLE_ID
FROM dbo.CUST_SVS_RQST
INNER Join dbo.TEST_EVENT
ON dbo.CUST_SVS_RQST.SERV_RQST_ID = dbo.TEST_EVENT.SERV_RQST_ID
INNER JOIN dbo.TEST_EVENT_SAMPLE
ON dbo.TEST_EVENT.TST_EVENT_ID = dbo.TEST_EVENT_SAMPLE.TST_EVENT_ID
GROUP BY dbo.CUST_SVS_RQST.SERV_RQST_ID,
dbo.TEST_EVENT.TST_EVENT_ID,
dbo.TEST_EVENT_SAMPLE.SAMPLE_ID,
dbo.TEST_EVENT.TST_GRP_ID
HAVING (dbo.TEST_EVENT.TST_GRP_ID = 3)) AS IMCL_SAMPLE
GROUP BY IMCL_SAMPLE.SERV_RQST_ID
Edited by - Scorpion_66 on 11/04/2002 2:11:27 PM