Need help with how to get a count of unique values in a table

30 Sep 2005 10:13 PM

kevin

Topics sounds confusing, right? Welcome to my world!

Here's an example. I have a database of phone numbers, such as this:

table: members
field: phone_number

values:
123-456-xxxx
123-234-xxxx
324-667-xxxx
555-767-xxxx
555-876-xxxx
661-661-xxxx
555-555-xxxx
324-324-xxxx

I need to know how many unique area codes there are, and how many
instances of that areas code

e.g.
123 (2)
324 (2)
767 (3)
661 (1)

How, in an SQL SELECT statement, can I do that? I'm not new to SQL, but

this one has me baffled!

30 Sep 2005 10:21 PM

Jerry Spivey

Kevin,

Try this:

SELECT LEFT(PHONE,3) AS 'PRE', COUNT(*) AS 'COUNT'
FROM PHONENUMBERS
GROUP BY LEFT(PHONE,3)

HTH

Jerry
<ke***@questionmark.com> wrote in message
Show quote

news:1128118380.580703.221780@z14g2000cwz.googlegroups.com...
> Topics sounds confusing, right? Welcome to my world!
>
>
> Here's an example. I have a database of phone numbers, such as this:
>
>
> table: members
> field: phone_number
>
>
> values:
> 123-456-xxxx
> 123-234-xxxx
> 324-667-xxxx
> 555-767-xxxx
> 555-876-xxxx
> 661-661-xxxx
> 555-555-xxxx
> 324-324-xxxx
>
>
> I need to know how many unique area codes there are, and how many
> instances of that areas code
>
>
> e.g.
> 123 (2)
> 324 (2)
> 767 (3)
> 661 (1)
>
>
> How, in an SQL SELECT statement, can I do that? I'm not new to SQL, but
>
> this one has me baffled!
>

1 Oct 2005 12:08 AM

kevin

Thanks, Jerry. Looking forward to getting back into the office to try
this out :) I'll let you know how I get on.

3 Oct 2005 2:02 PM

kevin

Thanks again, Jerry. Other than having to use SUBSTR instead of LEFT,
your solution was just what I needed.

2 Oct 2005 1:35 AM

--CELKO--

SELECT X.area_exchange, COUNT(X.tally) AS tally
FROM (SELECT SUBSTRING (.phone_nbr, 1, 7),
COUNT (SUBSTRING (.phone_nbr, 1, 7))
FROM Foobar ) AS X(area_exchange, tally)
GROUP BY area_exchange;