How can I sum the same column twice but have one count only unique values?

[lilp32]

I have a table in an Access 2010 database. The table contains the fields Subject and Test_Number. Each subject and test number are listed in one row. Subjects can have multiple tests so there are duplicates in the Subject column. Tests can have multiple results, so there are duplicates in the Test_Number field. I would like to end up with one row per subject with Test_Number counted as follows:

1. "Results" - the total count of Test_Number for each subject including duplicates.
2. "Tests" - the total count of unique Test_Number per subject. The code below only has the column with duplicates.

I have tried using a nested subquery with select distinct but have not been able to get the syntax right.

SELECT [TABLE1].Subject, Count([TABLE1].Test_Number) AS Results
FROM [TABLE1]
GROUP BY [TABLE1].Subject;
 

[Rabbit]

Create a query that does a count on distinct records and join it to that query.

You should also know that your design is unnormalized and the results really should be its own separate table.

[NeoPa]

Rabbit's idea can also be used with a subquery (Subqueries in SQL).

Alternatively, you could use a subquery where the records are already GROUPed by [Subject] and use Sum() for [Results], but Count() for [Tests]. I think that's the right way around, but hopefully you get the picture.

[lilp32]

Thanks, I was able to get this working by creating a second query with the distinct count and joining it as Rabbit suggested. I am still trying to understand subqueries. Thanks again.

[NeoPa]

Lilp32:
I am still trying to understand subqueries

You and many others. I first got into them after Rabbit posted something on this site a few years back. Now I use them quite extensively. That's not the only SQL pointer I've picked up from Rabbit either. He's really very good, if sometimes a little short on explanatory detail when posting :-D