Posted by Roy Harvey on 08/13/07 14:38

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@yahoo.co.uk>
wrote:

>On 13 Aug, 13:26, Erland Sommarskog <esq...@sommarskog.se> wrote:
>> Clive Swan (clives...@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, esq...@sommarskog.se
>>
>> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
>> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>>
>> - Show quoted text -
>
>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
>
>

[Back to original message]