Counts query access

[rhonda6373]

Is it possible to have counts from two different tables in one query? I tried this and it worked, but I have duplicate rows with the same count numbers.

[NeoPa]

Yes it is. What are you trying to do? It's very possible that what you want is not supported but your question is very open.

[rhonda6373]

The business scenario is that I have multiple test cases for single template IDs. The template IDs are stored in one table and the test cases are stored in another table. The common field is the TemplateID.

I need a count of both the number of TemplateIDs as well as the total count of test cases in a single query.

I was attempting to get a Pivot table with both counts and that are also spliced by status.

I am open to any other solutions.

[NeoPa]

I would create a GROUP BY query that has another GROUP BY query as a subquery (See Subqueries in SQL).

Something like :

SELECT   Count([TemplateID] AS TemplateIDs
       , Sum([TestCases]) AS AllTestCases
 
FROM
    (
    SELECT   [TemplateID]
           , Count([TestCase]) AS TestCases
    FROM     [tblTestCase]
    GROUP BY [TemplateID]
    )

I would create a GROUP BY query that has another GROUP BY query as a subquery (See Subqueries in SQL).

Something like :

SELECT   Count([TemplateID] AS TemplateIDs
       , Sum([TestCases]) AS AllTestCases
 
FROM
    (
    SELECT   [TemplateID]
           , Count([TestCase]) AS TestCases
    FROM     [tblTestCase]
    GROUP BY [TemplateID]
    )

There is no GROUP BY clause in the main query as all records are grouped together.