Posted by Erland Sommarskog on 08/13/07 12:26

Clive Swan (cliveswan@yahoo.co.uk) writes:
> My GIS software has a tool to count the number of points within a grid.
> This is fine for small recordset, when you get into the tens thousands
> it becomes unfriendly.
>
> It must be possible (more efficent??) to do a select statement from
> the two tables and insert the result into a column??
>
> Table Property has thousands of records that fall within each record
> of Table Ward.
>
> Expect the SQL would be
>
> SELECT [Property].BedRmNumber FROM [Ward].LA
> WHERE [Property].LA = [Ward].LA
>
> Surely this would need a loop.

Loops are rarely effective.

It is not very clear from your post what you want to do. You talk
about selecting a count, but the SELECT statement you have lists a
column.

Doing a very wild guess, this may be what you are looking for:

SELECT P.BedRmNumber, COUNT(*)
FROM Ward W
JOIN Property P ON W.LA = P.LA
GROUP BY P.BedRmNumber

The usual recommendation for these type of questions is that you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

The less you include of this, the more guesswork you will get in
response.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]