Transact SQL Question- Distinct, Count, ZipCodes- yay!

[lj4three]

Here's the deal, bizatches- i have a bunch of peeps in my db- table Members- that has Zip_Code as a column. What I want to do is have a list of all unique zip codes and then break down how many peeps per Zip_Code there are.

For example- 28202 is a zip_code for 3 members.

What is the best way to write a script for this?

I've fooled around with Distinct and Count, but can't seem to breakdown the query to the point where i get a listing of zip_codes and the number of members for that zip_code. Help would be appreciated, ya dig?

 

[VOR]

have sex with yourself, the answer will come.

 

[Toll Booth Willie]

Here's the deal, bizatches- i have a bunch of peeps in my db- table Members- that has Zip_Code as a column. What I want to do is have a list of all unique zip codes and then break down how many peeps per Zip_Code there are.

For example- 28202 is a zip_code for 3 members.

What is the best way to write a script for this?

I've fooled around with Distinct and Count, but can't seem to breakdown the query to the point where i get a listing of zip_codes and the number of members for that zip_code. Help would be appreciated, ya dig?

select zip_code, count(peeps) as Total_per_Code
from members
group by zip_code

 

[VOR]

see

 

[lj4three]

select zip_code, count(peeps) as Total_per_Code
from members
group by zip_code

you da man!

thanks bro.

 

[lj4three]

I got another problem. In my database, I have a lot of duplicate entries. The quickest way to detect these duplicates is by phone_number. I want to write a script that selects a phone_number, outputs its total number of times in the database, and thereafter, outputs the person(s) who have this phone_number. So, if phone_number 0000000 is only in the table once, then total_per_phone = 1, person_1 = BLAH. There are phone_numbers in the table that are inputted up to 4 times, so i basically want a result set like this:

phone_number, total_per_phone_number, person_1, person_2, person_3, person_4 (where person_1 through person_4 = last_name, first_name, spouse_name).

I have the initial script:

select phone_number, count(phone_number), as Total_per_phone
from Members
group_by phone_number

Any suggestions?

 

[chipshot]

What is your DB? Can you utilize stored procedures? Sly will asnwer you in the morning. :xyzthumbs

 

[lj4three]

What is your DB? Can you utilize stored procedures? Sly will asnwer you in the morning. :xyzthumbs

its a hindu temple's db- members info, account info, etc.

i can utilize sp's.

p.s. sly is not a crackpot

 

[chipshot]

I mean is it sql server?

 

[lj4three]

I mean is it sql server?

yes.