|
| PORTAIL | ANNUAIRE | ARTICLES | COMPARATEUR HÉBERGEURS | DEVIS | FORUMS | RÉDUCTEUR D'URL |
|
10/09/2007, 19h18
|
#1 |
Messages: n/a
Hébergeur: |
Re: INNER JOIN - INSERT
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.) |
|
 |
| Outils de la discussion | |
|
|
