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(zip,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(zip,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]

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]

Im running the query against dbf file.

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

 

[GhostInAK]

Hello JimmyKoolPantz,

Check out the GROUP BY and HAVING clauses.

-Boo

 

[Guest]

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]

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.