[dba-VB] Using the hash for real world problems

Caaution. All aircode below:

To get the list output below, I would use something like

Select Distinct  
   Count(HashAddr) as Addresses, People 
>From 
     (Select distinct 
           HashAddr,Count(HashAddr) as People 
      From 
           tblPeople
      Group By HashAddr)
Group By People
Order by People

To find out details for a *specific* Address

Find the Hash for the desired Address and then pass it to something like this:

Select Distinct 
    StreetNo,Street,City,Zip,State,Count(HashAddr) as Address
>From 
     tblPeople P Inner Join tblEmails E on P.PK  =  E.PeopleFK
Group By HashAddr StreetNo,Street,City,Zip,State
Where HashAddr = @SearchHash



On 19 Mar 2011 at 10:13, jwcolby wrote:

> Ooops.  My examples were hosed.
> 
> Let's try again
> 
> Addresses	People
> 22,538,240	1
> 00,708,462	2
> 00,052,234	3
> 
> and so forth.
> 
> What the client really wants to know is
> 
> "how many people with emails are at a specific address"
> "how many people with emails are in a specific family"
> 
> etc.
> 
> I tie my address table to my email table using an auto increment long
> increment "I must be careful not to call this a surrogate" key and
> then perform the counts using the hash fields in a Group By / count
> query.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> On 3/19/2011 9:55 AM, jwcolby wrote:
> > I got into the hashing business in order to create a surrogate key
> > of sorts, in fact a trio of them.
> >
> > I have an address hash, a family hash and a person hash. I compute
> > these three hashes for every table of persons. Now that I have these
> > surrogate keys I have "identical" values in a single field where the
> > input is identical in multiple fields. Thus I do not have to do
> > multi-field joins where I have a hash for all of those fields.
> >
> > I am happy to hear that I am secure in my surrogate hash algorithm
> > choice.
> >
> > OK, so I have these hashes. Every record has these three hashes.
> >
> > Now some definitions:
> >
> > HashAddr: A unique address is addr/zip5/zip4 different
> > HashFamily: A unique family is LName/Addr/Zip5/Zip4 different
> > HahsPerson: A unique person is FName/LName/Addr/Zip5/Zip4 different.
> >
> > Using this information, I need to calculate the count of addresses
> > with a single person, with two persons etc for as many combinations
> > as I have.
> >
> > Addresses People
> > 22,538,240 1
> > 780,462,346 2
> > 52,234 3 people
> > etc
> > etc till every quantity of people is found.
> >
> > What is the simplest way to accomplish this with SQL.
> >
> >
> >
> >
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
>