Posted by Ed Murphy on 09/10/07 18:18

Erland Sommarskog wrote:

> Clive Swan (cliveswan@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

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

• Next in forum: Re: Compare based on table values.
• Prev in forum: Re: Identity Column in INFORMATION_SCHEMA or the sys tables
• Thread view: Re: INNER JOIN - INSERT

[Reply to this message]