Counting distinct records across two tables

Question

Question concerning counting unique combination of fields across two tables in SQL. MSaccess 2003.

table1: id, fld1, fld2
table2: id, dateAndTime

id is not unique; tables are joined across "id".

I need a count, by date (dd/mm/yy - ignoring time) of distinct combinations of fld1 and fld2 (int).

More specifically, I need to know which date has the most distinct combinations of fld1 and fld2.

If table1 has

1, 101, 101
2, 101, 101
3, 101, 101
4, 101, 102
5, 101, 102
6, 101, 103

And table 2 has

1, 12/1/2010
2, 12/1/2010
3, 12/1/2010
1, 12/2/2010
2, 12/2/2010
4, 12/2/2010
5, 12/2/2010
6, 12/2/2010

I need

12/1/2010, 1     'only 1 unique combinatin of fld1 and fld2
12/2/2010, 3     'only 3 unique combinations of fld1 and fld2

But I only need "12/2/2010, 3" output as I just need the date and count of the largest count.

Couldn't figure out how to format this in the Commment to the correct answer below - so here it is for MS Access 2003.

Select TOP 5 theDay, count(*) AS theCount 
FROM ( 
Select cdate(int(date_col)) As theDay 
From tbl1 Inner Join tbl2 on tbl1.id=tbl2.id 
Group By cdate(int(date_col)), fld1, fld2 
) As X 
Group By theDay 
Order By 2 Desc;

This returns the TOP 5 combinations by Date (disregarding any time value)

Sorry, this is my first time posting to stackoverflow, but I have used it numerous times to find answers. I have tried numerous things and get vague (to me) errors about agregate functions, etc. I am a very simple user of SQL and just know the basics about joining and selecting and where clauses. — JJJones_3860, Mar 18 '13 at 22:43
Remember to supply your database system and version . Date / time handling differs a lot between flavors. *Edit* your question for that. — Erwin Brandstetter, Mar 18 '13 at 22:46

Erwin Brandstetter · Accepted Answer · 2013-03-19T22:15:33.960

0

In modern RDBMS you can count distinct combinations in one go:

SELECT date_col, count(DISTINCT (fld1, fld2)) AS unique_combos
FROM   tbl1
JOIN   tbl2 USING (id)
GROUP  BY date_col;

Simpler method in two steps if that does not work:

SELECT date_col, count(*) AS unique_combos
FROM  (
   SELECT date_col
   FROM   tbl1
   JOIN   tbl2 ON tbl1.id = tbl2.id
   GROUP  BY date_col, fld1, fld2
   ) AS x
GROUP  BY date_col;

The second one should work in MS Access, too.

What @user2184214 finally went with:

SELECT TOP 5 theDay, count(*) AS theCount
FROM  (
   SELECT cdate(int(date_col)) AS theDay
   FROM   tbl1 
   JOIN   tbl2 ON tbl1.id = tbl2.id
   GROUP  BY cdate(int(date_col)), fld1, fld2
   ) AS x
GROUP  BY theDay
ORDER  BY 2 DESC;

edited Mar 19 '13 at 22:15

answered Mar 18 '13 at 23:04

Erwin Brandstetter

605,456
145
1,078
1,228

Ok - figured this out... thanks. It is so confusing. Select TOP 5 theDay, count(*) AS theCount FROM ( Select cdate(int(date_col)) As theDay From tbl1 Inner Join tbl2 on tbl1.id=tbl2.id Group By cdate(int(date_col)), fld1, fld2 ) As X Group By theDay Order By 2 Desc; This returns the TOP 5 combinations by Date (disregarding any time value) – JJJones_3860 Mar 19 '13 at 21:31
@user2184214: Cool! :) I added a formated version of your final solution to the answer. Maybe add `HAVING count(*) > 1` to weed out dates with a single combination? Consider deleting your earlier comments, which are superseded by your last. – Erwin Brandstetter Mar 19 '13 at 22:17

Bohemian · Answer 2 · 2013-03-19T20:43:09.300

0

This query gives you what you want:

SELECT
  date(dateAndTime) AS date,
  count(DISTINCT (fld1, fld2)) AS count
FROM table1
JOIN table2 ON table1.id = table2.id
GROUP BY 1
HAVING count(DISTINCT fld1, fld2) > 1
ORDER BY 2 DESC

edited Mar 19 '13 at 20:43

answered Mar 19 '13 at 00:18

Bohemian

412,405
93
575
722

For MSAccess2003, this gives "Syntax error (missing operator) in query expression 'count(Distinct fld1, fld2)'." – JJJones_3860 Mar 19 '13 at 19:21

Counting distinct records across two tables

2 Answers2