Hi,

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.

Could anyone help???

Thanks

clive

Clive Swan (cliveswan (AT) yahoo (DOT) co.uk) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On 13 Aug, 13:26, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Hi,

Hope this is clearer.

I have a one-to-many relationship between [Ward].LA
and [Property].BedroomNumber.

For example
[Property].BedroomNumber [Property].LA
1 00AA
5 00AA
10 00AA
15 00AA
20 00AA
10 00AA
25 00AA

1 00AB
1 00AB
2 00AB
1 00AB
20 00AB
10 00AB
25 00AB

[Ward].LA
00AA
00AB
00AC
00AD
00AE
00AF

[Ward] may have 10,000 records while [Property] might have
1 million records.

I want to count and add up all the [Property].BedroomNumber with
a unique [Property].LA, then add the result set to
[Ward].BedroomNumber.

So that I would have the following result:

[Ward].LA [Ward].BedroomNumber
00AA 78
00AB 60
00AC 10
00AD 100
00AE 150
00AF 20

Perhaps this will get you started.

SELECT [Ward].LA,
COUNT([Ward].BedroomNumber) as Rooms
FROM [Ward]
GROUP BY [Ward].LA
ORDER BY [Ward].LA

Roy Harvey
Beacon Falls, CT

On Mon, 13 Aug 2007 06:26:45 -0700, Clive Swan <cliveswan (AT) yahoo (DOT) co.uk>
wrote:

Clive Swan (cliveswan (AT) yahoo (DOT) co.uk) writes:
Maybe:

UPDATE Ward
SET BedroomNumber = P.cnt
FROM Ward W
JOIN (SELECT LA, COUNT(*) AS cnt
FROM Property
GROUP BY LA) P ON W.LA = P.LA

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On 13 Aug, 22:58, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Thanks,

Will give that a try.

Clive

Erland Sommarskog wrote:

SUM() rather than COUNT(), surely? Look again at his desired
results. (Okay, so 78 is too low, but 60 is spot on.)