SQL DISTINCT COUNT

23 Sep 2006 5:21 PM

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

23 Sep 2006 6:29 PM

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 wrote:
Show quoteHide quote

> 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

23 Sep 2006 7:45 PM

JimmyKoolPantz

Im running the query against dbf file.

rowe_newsgroups wrote:
Show quoteHide quote

> 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(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

23 Sep 2006 8:31 PM

GhostInAK

Hello JimmyKoolPantz,

Check out the GROUP BY and HAVING clauses.

-Boo

Show quoteHide quote

> 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
>

23 Sep 2006 8:43 PM

Kerry Moorman

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

Show quoteHide quote

"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(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
>
>

24 Sep 2006 3:37 AM

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.
Kerry Moorman wrote:
Show quoteHide quote

> 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(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
> >
> >