Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
starnold
Yak Posting Veteran
83 Posts |
 Posted - 2008-09-30 : 11:35:03
|
| I want to update a table with how many times a particular address & Postcode occurs, the data is below:URN Name ADD1 PCODE count123456987 3 street2 888888888 987123456 4 street2 888888888 123456789 1 street1 999999999 987654321 2 street1 999999999 What I want to do is update the count column, grouping by ADD1 & PCODE. So the final data would look like:URN Name ADD1 PCODE count123456987 3 street2 888888888 2987123456 4 street2 888888888 2 123456789 1 street1 999999999 2 987654321 2 street1 999999999 2I need to run this across a database of about 3.5 million rows! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-30 : 11:41:16
|
| [code]Update y SET y.Count=t.CountValFROM yourtable yJOIN(SELECT count(URN) AS CountVal, PCODEFROm YourTableGROUP BY PCODE) tON t.PCODE=y.PCODE[/code] |
 |
|
|
starnold
Yak Posting Veteran
83 Posts |
Posted - 2008-09-30 : 11:43:48
|
| It would need to also group by the street? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-30 : 11:45:51
|
[code]UPDATE y SET y.Count = t.CountValFROM yourtable AS yINNER JOIN ( SELECT count(URN) AS CountVal, PCODE, Add1 FROM YourTable GROUP BY PCODE, Add1 ) AS t ON t.PCODE = y.PCODEWHERE y.Add1 = t.Add1[/code]
E 12°55'05.63"N 56°04'39.26" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-30 : 11:48:24
|
quote:
Originally posted by starnold
It would need to also group by the street?
yup. it need to. just noticed original post that you want count grouped by both. so add street field also to group by and to join condition. |
|
|
|
|
|
|
Count Query