CODEWITHSUNDEEP
sqlms-access
JJJones_3860
JJJones_3860

Reputation: 1542

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.

I want a Count of Distinct fld1+fld2 values Grouped By Book and Name.

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, Name
1, "John"
2, "John"
3, "John"
1, "Tom"
3, "Tom"
3, "Jack"


Table2
ID, Book, Fld1, Fld2
1, "A", 1, 1
2, "A", 1, 2
2, "A", 1, 2
1, "B", 1, 1
2, "B", 1, 3
3, "B", 1, 3

The expected result set:

Book, Name, Count()
A, John, 2
A, Tom, 1
A, Jack, 0
B, John, 2
B, Tom, 2
B, Jack, 1

Thanks.

Upvotes: 0

Views: 207

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

This seems like a basic JOIN and GROUP BY:

select t2.fld1, t2.fld2, count(*)
from t1 inner join
     t2
     on t1.id = t2.id
group by t2.fld1, t2.fld2;

I'm not sure what you mean by "unique combinations".

Upvotes: 0

Related Questions