SQL Aggregate Count(*) - selecting specific count range in where clause

[Canuck]

I am doing a simple aggregate count, where I am counting the number of point objects inside a boundary object.  All boundary objects have unique ID's so I can group by this field.  

The where condition at the beginning is the basic 

where: Boundary.obj contains point.obj

Under select columns it's also simple  ID, Count(*) "count"

Group by column: ID

my question is what do I add to the where condition, so that my results only give me boundaries where the total count is greater than or equal to 2?

Boundary.obj contains point.obj AND ??????

everything I put after the and..."count" >=2, or Count(>=2), ETC.  I keep getting variable or field count, not defined

Can I even do this in a single query, or do I have to save these results first and then query from that?  I'd rather avoid having to do that if I can do it all in 1 query.

Thanks in advance,

Ryan

[Peter Horne]

HJi Canuck,

fairly certain you will need two queries.
Seems you can use calculated columns in the Group by or Order by sections of SQL but not use them as "filters" in the Condition Part.

I stand (and hope) to be corrected though.............

Thanks
Peter

[Richard Greenwood]

Peter is correct in that you need 2 queries. An easy way in MapInfo is to do your first query which will put the results into "Query1" and then do the second query against Query1 where the "count" column is greater than 1.

Another way is to do a sub query in the Where box of the MapInfo SQL Select dialog. Something like 

count > 1 from 

(select ID, Count(*) "count" 

from Boundary, point

where Boundary.obj contains point.obj 

Group by ID)

I'm doing this off the top of me head so forgive me if my syntax is wrong or if I'm not understanding your question.

Rich

--
--
You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en

---
You received this message because you are subscribed to the Google Groups "MapInfo-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mapinfo-l+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Richard W. Greenwood, PLS
www.greenwoodmap.com

[Andrew Harfoot]

The SQL syntax that you would need to use for a filter on an aggregate column is the HAVING keyword, however, MapInfo does not support this, so two separate queries are required.

A second issue is that MapInfo does not carry through the object geometry in an aggregate query, so the  table of boundaries with counts won't be mappable. A subquery would allow the boundaries to be selected that contained greater than 2 points, but without preserving the count values themselves. A join would carry over the count values, but would require that the aggregate query table be saved to disk. My preferred solution is to use the Update column command to create a temporary column with the counts of points instead of a query. The table then remains mappable.

Cheers,

Andy

To unsubscribe from this group and stop receiving emails from it, send an email to mapinfo-l+...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

-- 
Andy Harfoot

GeoData Institute
University of Southampton
Southampton
SO17 1BJ

Tel:  +44 (0)23 8059 2719

www.geodata.soton.ac.uk

[Canuck]

Thank you to all who replied.  It does look like 2 queries are required, which I have done.  Not a huge deal.  I was writing these into an MBX as this is a process I will have to repeat often, so I just have to write the code into the MBX once, even though it requires 2 queries, I will not have to do it manually going forward.

Thanks again.  It's always great to get help in this forum.