|
|
Posted by Clive Swan on 08/13/07 13:26
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]
|