Counting duplicates

[chandler]

I have a table with address info and job numbers. There are about 30 different job numbers and I want to get a count of how man duplicates there are between job numbers. For example, how many duplicates are there between job number 55326A and 55326B, 55326A and 55326C et al.

The first step, I assume, is to select out the duplicates. Then somehow write a routine that will figure out how many job numbers there are and then go down the list and count the duplicates?



Chandler
I ran over my dogma with karma!

 

[craigsboyd]

chandler,

Use a SQL select statement to do both at the same time...

If it's duplicate jobnums you are looking for:

SELECT CNT(*) as cnt, jobnum from mytable GROUP BY jobnum HAVING cnt(*) > 1

If it's duplicate addresses you are looking for:

SELECT CNT(*) as cnt, address, jobnum from mytable GROUP BY address HAVING cnt(*) > 1

...on the second of these SQL Select statement you may run into a problem on VFP8 or above...I believe they tightened the group by SQL compatability in VFP8 so that the select portion of the query can only contain aggregates or fields that are included in the group by clause. Anyways, if you get some weird Group By invalid errors then you can rework your SQL so it works or you can just use:

SET ENGINEBEHAVIOR 70

http://www.sweetpotatosoftware.com

 

[chandler]

On the right path.. but I guess I need to be more descriptive. I have about 200,000 records that fall within about 30 job numbers. Many of those records have duplicate address information, but a different job number. I need a count of the overlap between the job numbers on the address info. IE, duplicate addresses between job 1234A and job 1234B. job 1234A and job 12345C, etc. I guess this is sort of a duplicate/combination/permutation problem. I failed stats twice and the comb/perm thing is beyond me. thanks for your help!

Chandler
I ran over my dogma with karma!

 

[craigsboyd]

Look in:

All Combinations
thread184-823742

I posted code in there for finding permutations and combinations...it may help you in your pursuit.

http://www.sweetpotatosoftware.com