user2184214 user2184214 - 3 months ago 12
SQL Question

Count distinct pair of values grouped by other values

Access 2013 SQL.

I have 2 tables that I am joining over a shared "ID" field.

Table1 fields: ID, ID2
Table2 fields: ID, ID3, fld1, fld2


I want a Count of Distinct fld1+fld2 values Grouped By ID2 and ID3.

How to do this in Access 2013 SQL please. I have nothing that doesn't produce a syntax error or other aggregate error after 2 hours of trying.

Example data:

Table1
ID, ID2
1, "Denver"
2, "Chicago"
1, "Denver"
1, "Chicago"

Table2
ID, ID3, Fld1, Fld2
1, "John", 1, 1
2, "Tom", 1, 1
1, "John", 1, 2
2, "John", 2, 3
1, "Tom", 1, 1
2, "Tom", 1, 2
2, "Tom", 1, 1


The expected result set:

ID2, ID3, Count()
Denver, John, 2
Denver, Tom, 1
Chicago, John, 1
Chicago, Tom, 2


My confusion obviously stems from how to define the problem. Thanks for your help. :)

Thanks.

Answer Source

Make the data distinct before aggregating:

select id2, id3, count(*)
from
(
  select distinct t1.id2, t2.id3, t2.fld1, t2.fld2
  from table1 t1
  join table2 t2 on t2.id = t1.id
) t
group by t.id2, t.id3;