Query data with count function

[TinaA...@gmail.com]

We are using StreetPro EAL data and are having some issues with it.
The users are creating maps for a specific zip code. This works fine,
but we want to be able to identify the street segments that have two
different names associated with the segment. An example would be a
segment with the name 1st Ave and 1st St. We know they are the same
segment because the Start_X, Start_Y, End_X and End_Y values are the
same.

In MapInfo, we try to create a SQL Select query to show the segments
that have a count greater than 1. The query I create looks like this:
Select columns: START_X, START_Y, END_X, END_Y, count(*)
from tables: Streets
Group by columns: START_X, START_Y, END_X, END_Y

But I am unsure how to get the query to return just rows where the
count(*) is greater than 1. I have tried aliases the count(*) field
but it does not like my syntax.

Then once I have this query working in Map Info, I have tried to take
the query and use it in Map Basic. This will allow the users to run
the command from a tool bar without having to enter the information
every time. But the query always just returns 1 row with the total
count. Here is the query I am running there:
Run Command "Select START_X, END_X, START_Y, END_Y, count(*) from
STREETS ORDER BY START_X, END_X, START_Y, END_Y into qryDups"

Shouldn't this query work the same in Map Basic as it does in Map
Info? Has anyone had experience with trying to return counts in this
manner?

Eventually I want to show the street names, zips and counts for
segments that have more than one street name associated with them.

Thanks in advance for any help with this.
Tina

[Spencer Simpson]

You need two successive selects. The first does the grouping and the second
does the filtering:

Select START_X, END_X, START_Y, END_Y, count(*)"Ct" from STREETS into
qryGroups noselect

Select START_X, END_X, START_Y, END_Y, Ct from qryGroups where ct > 1 into
qryDups NoSelect ORDER BY START_X, END_X, START_Y, END_Y

Aliasing may or not work depending on your version of MapInfo; if it
doesn't, you'll have to use the name _COL5 in the second query instead of
the alias "Ct" I gave the count aggregate column.

Hope this helps
Spencer

[Spencer Simpson]

Of course, you need a group by clause.

Select START_X, END_X, START_Y, END_Y, count(*)"Ct" from STREETS into

qryGroups noselect group by START_X, END_X, START_Y, END_Y

[TinaA...@gmail.com]

OK, this seems to work. My original query was using the Order By
clause instead of the Group By.

Second question. Is there a way to select FullName from the Streets
table for the duplicate records?

With this query, I will need to join in the Streets table to display
the FullName field.

Select START_X, END_X, START_Y, END_Y, Ct from qryGroups where ct
> 1 into
qryDups NoSelect ORDER BY START_X, END_X, START_Y, END_Y

Thanks for your help on this!!

On Mar 16, 12:18 pm, "Spencer Simpson" <ssimp...@baltometro.org>
wrote: