Subquery in Microsoft Access

Question

Does anyone know the changes needed to this code to get the count of the unique instances of DistinctThings instead of just the count? At the moment when I run the code I just get the count. DistinctThings are in col f1.

So what I was trying to achieve is a list of three cols, with the unique dates (col f15), then the count of the distinct things (col f1) then the sum of the values (col f16)

Thanks in advance!

SELECT f15, sum(f16), Count(*) AS DistinctThings
FROM (SELECT DISTINCT f1, f15, f16 FROM Table)
group by f15
;

Dummy data:

F1       F15           F16
Bob     01/12/2017     100
Harry   02/12/2017     200
James   03/12/2017     300
Bob     01/12/2017     100
Harry   02/12/2017     200
James   03/12/2017     300
Bob     01/12/2017     100
Harry   02/12/2017     200
James   03/12/2017     300

Sam     04/12/2017     400
Bob     01/12/2017     17
Harry   02/12/2017     68
James   03/12/2017     33
Sam     04/12/2017      4
Bob     01/12/2017     13
Harry   02/12/2017     234
James   03/12/2017     324
Sam     04/12/2017     57

Result I get when I run the query:

f15     DistinctThings   Total

01/12/2017      1            216.5
02/12/2017      1            214.5
03/12/2017      1            209.5
04/12/2017      1            172.5

So the total is col f16 and I would like DistinctThings to be the count of the distinct number of names per distinct date

Answer 1

This should do:

SELECT 
    f15, 
    Count(*) As DistinctThings,
    Sum(s16) As Total
FROM 
    (SELECT f15, Sum(f16) As s16 FROM Table Group By f15)
group by 
    f15

Edit:

Try this subquery:

    (SELECT f15, Sum(f16) As s16 FROM Table Group By f15, f1)