Posted by Clive Swan on 08/15/07 09:37

On 13 Aug, 22:58, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Clive Swan (clives...@yahoo.co.uk) writes:
> > 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
>
> 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, 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 -

Thanks,

Will give that a try.

Clive

[Back to original message]