SQL DISTINCT COUNT

[JimmyKoolPantz]

for some reason I can't seem to figure this out.

Situation: I'm using vb.net to create a query that will populate a
dataset with zipcode and count that have unique first 3 digits. I want
to create an audit that shows the client that he has so many reocords
in a 3 digit zip.

sampe data
name | zip
john 32118
joe 32114
mike 32112
tom 41111
tim 41121


table needed
3digitzip | count
321 3
411 2


I've tried a few things but I'm just running in circles and losing what
logic I though I had.

my query somewhat looks like this but i have tried a few other querys
that did not work.

QY = "select Distinct(Left(z ip,3) as ZIPCODE, count(left(zip, 3) as
QUANTITY"

The afformentioned query does not give me the totals I need.

I've tried something like:
QY = "select Distinct(Left(z ip,3) as ZIPCODE, count(distinct
left(zip,3) as QUANTITY"

on the above querry I would get an error in visual studio "missing
opererator" error

[rowe_newsgroups]

Re: SQL DISTINCT COUNT

Is the database you're using support temporary tables? If so you could
try "two-stepping" it, by populating a temp table with the left 3
digits of the zip codes, and them doing a count query on the temp
table. By the way, if what I said doesn't work, you may try posting in
a dedicated SQL group for help with your query.

Thanks,

Seth Rowe

JimmyKoolPantz wrote:

for some reason I can't seem to figure this out.
>
Situation: I'm using vb.net to create a query that will populate a
dataset with zipcode and count that have unique first 3 digits. I want
to create an audit that shows the client that he has so many reocords
in a 3 digit zip.
>
sampe data
name | zip
john 32118
joe 32114
mike 32112
tom 41111
tim 41121
>
>
table needed
3digitzip | count
321 3
411 2
>
>
I've tried a few things but I'm just running in circles and losing what
logic I though I had.
>
my query somewhat looks like this but i have tried a few other querys
that did not work.
>
QY = "select Distinct(Left(z ip,3) as ZIPCODE, count(left(zip, 3) as
QUANTITY"
>
The afformentioned query does not give me the totals I need.
>
I've tried something like:
QY = "select Distinct(Left(z ip,3) as ZIPCODE, count(distinct
left(zip,3) as QUANTITY"
>
on the above querry I would get an error in visual studio "missing
opererator" error

[JimmyKoolPantz]

Re: SQL DISTINCT COUNT

Im running the query against dbf file.

rowe_newsgroups wrote:

Is the database you're using support temporary tables? If so you could
try "two-stepping" it, by populating a temp table with the left 3
digits of the zip codes, and them doing a count query on the temp
table. By the way, if what I said doesn't work, you may try posting in
a dedicated SQL group for help with your query.
>
Thanks,
>
Seth Rowe
>
JimmyKoolPantz wrote:

for some reason I can't seem to figure this out.

Situation: I'm using vb.net to create a query that will populate a
dataset with zipcode and count that have unique first 3 digits. I want
to create an audit that shows the client that he has so many reocords
in a 3 digit zip.

sampe data
name | zip
john 32118
joe 32114
mike 32112
tom 41111
tim 41121


table needed
3digitzip | count
321 3
411 2


I've tried a few things but I'm just running in circles and losing what
logic I though I had.

my query somewhat looks like this but i have tried a few other querys
that did not work.

QY = "select Distinct(Left(z ip,3) as ZIPCODE, count(left(zip, 3) as
QUANTITY"

The afformentioned query does not give me the totals I need.

I've tried something like:
QY = "select Distinct(Left(z ip,3) as ZIPCODE, count(distinct
left(zip,3) as QUANTITY"

on the above querry I would get an error in visual studio "missing
opererator" error

[GhostInAK]

Re: SQL DISTINCT COUNT

Hello JimmyKoolPantz,

Check out the GROUP BY and HAVING clauses.

-Boo

for some reason I can't seem to figure this out.
>
Situation: I'm using vb.net to create a query that will populate a
dataset with zipcode and count that have unique first 3 digits. I
want to create an audit that shows the client that he has so many
reocords in a 3 digit zip.
>
sampe data
name | zip
john 32118
joe 32114
mike 32112
tom 41111
tim 41121
table needed
3digitzip | count
321 3
411 2
I've tried a few things but I'm just running in circles and losing
what logic I though I had.
>
my query somewhat looks like this but i have tried a few other querys
that did not work.
>
QY = "select Distinct(Left(z ip,3) as ZIPCODE, count(left(zip, 3) as
QUANTITY"
>
The afformentioned query does not give me the totals I need.
>
I've tried something like:
QY = "select Distinct(Left(z ip,3) as ZIPCODE, count(distinct
left(zip,3) as QUANTITY"
on the above querry I would get an error in visual studio "missing
opererator" error
>

[Kerry Moorman]

RE: SQL DISTINCT COUNT

JimmyKoolPantz,

In SQL Server you could do something like this:

Select Left(zip,3) as ZIPCODE, Count(left(zip, 3)) as QUANTITY From MyTable
Group By Left(zip,3)

Kerry Moorman


"JimmyKoolPantz " wrote:

for some reason I can't seem to figure this out.
>
Situation: I'm using vb.net to create a query that will populate a
dataset with zipcode and count that have unique first 3 digits. I want
to create an audit that shows the client that he has so many reocords
in a 3 digit zip.
>
sampe data
name | zip
john 32118
joe 32114
mike 32112
tom 41111
tim 41121
>
>
table needed
3digitzip | count
321 3
411 2
>
>
I've tried a few things but I'm just running in circles and losing what
logic I though I had.
>
my query somewhat looks like this but i have tried a few other querys
that did not work.
>
QY = "select Distinct(Left(z ip,3) as ZIPCODE, count(left(zip, 3) as
QUANTITY"
>
The afformentioned query does not give me the totals I need.
>
I've tried something like:
QY = "select Distinct(Left(z ip,3) as ZIPCODE, count(distinct
left(zip,3) as QUANTITY"
>
on the above querry I would get an error in visual studio "missing
opererator" error
>
>

[JimmyKoolPantz]

Re: SQL DISTINCT COUNT

Kerry Moorman,

Thanks, you are correct. I finally figured it out, however, you
solution was about 4 hours quicker than mine. I think what really hurt
me was I was trying to use the keyword distinct. And then, after
trying, trying, and then crashing I became confused. I read alot of
documentation but never once found something on the internet that was
using left in the group by clause.

Thanks again.
Kerry Moorman wrote:

JimmyKoolPantz,
>
In SQL Server you could do something like this:
>
Select Left(zip,3) as ZIPCODE, Count(left(zip, 3)) as QUANTITY From MyTable
Group By Left(zip,3)
>
Kerry Moorman
>
>
"JimmyKoolPantz " wrote:
>

for some reason I can't seem to figure this out.

Situation: I'm using vb.net to create a query that will populate a
dataset with zipcode and count that have unique first 3 digits. I want
to create an audit that shows the client that he has so many reocords
in a 3 digit zip.

sampe data
name | zip
john 32118
joe 32114
mike 32112
tom 41111
tim 41121


table needed
3digitzip | count
321 3
411 2


I've tried a few things but I'm just running in circles and losing what
logic I though I had.

my query somewhat looks like this but i have tried a few other querys
that did not work.

QY = "select Distinct(Left(z ip,3) as ZIPCODE, count(left(zip, 3) as
QUANTITY"

The afformentioned query does not give me the totals I need.

I've tried something like:
QY = "select Distinct(Left(z ip,3) as ZIPCODE, count(distinct
left(zip,3) as QUANTITY"

on the above querry I would get an error in visual studio "missing
opererator" error