Update table with sums of groups

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update table with sums of groups

Author

Topic

gscgis
Starting Member

6 Posts

Posted - 2009-03-04 : 16:21:51

This is likely a VERY basic question.

I have a table called COMPARTMENTS that has some fields like below:

COMPARTMENT AREA_METRES TOTAL_COMP_ACRES
1 5
2 10
3 4
1 2
2 8
1 6

I would to update the table (the TOTAL_COMP_ACRES field) with the total sum of acres for each group. So the table will end up looking like this (just an example not actual acreage values) with repeating areas (each compartment should have the same area).

COMP AREA_METRES TOTAL_COMP_ACRES
1 5 13
2 1 18
3 4 4
1 2 13
2 8 18
1 6 13

I was hoping to use a simple update query like this:

Update COMPARTMENTS set TOTAL_COMP_AREA=sum(AREA_METRES * 0.00247) group by COMP

Can anyone help me out? This does not work.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-04 : 16:41:30

Your expected output is not clear.Can you clarify?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-04 : 17:06:45

May be this....

update @t set t.total_comp_area = a.sum_area
from @t t join (select compartment , sum(area_metres) as sum_area from @t group by compartment) a
on t.compartment = a.compartment

But I dont understand the logic of multiplying by 0.00247 because ur expected output doesn't have that logic.

gscgis
Starting Member

6 Posts

Posted - 2009-03-05 : 08:53:43

Sorry I forgot a zero in the.000247 value (which is the conversion from Metres Squared to Acres). This part is really irrelevant (since I should just have to add the * 0.000247 to get this value).
My main concern is updating the TOTAL_COMP_ACRES with the sum of the areas for each compartment.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-05 : 09:32:00

Did u try the update query i posted?

gscgis
Starting Member

6 Posts

Posted - 2009-03-05 : 09:53:41

I tried running it directly from my sql server mgmt studio and it gave me an error

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "t.TOTAL_COMP_ACRES" could not be bound.

edit: sorry that was TOTAL_COMP_ACRES instead of GIS_ACREAGE

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-05 : 10:41:48

What is GIS_ACREAGE_COMPARTMENT? Its not there in your sample data.
Can you post the query that you tried?

gscgis
Starting Member

6 Posts

Posted - 2009-03-05 : 10:51:10

sorry that was TOTAL_COMP_ACRES instead of GIS_ACREAGE

update [PROPERTIES] set t.TOTAL_COMP_AREA = a.sum_area
from [PROPERTIES] t join (select [COMP_NUMBER] , sum([AREA]) as sum_area from [PROPERTIES] group by [COMP_NUMBER]) a
on t.COMP_NUMBER = a.COMP_NUMBER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-05 : 12:47:20

quote:
Originally posted by gscgis

sorry that was TOTAL_COMP_ACRES instead of GIS_ACREAGE

update t set t.TOTAL_COMP_AREA = a.sum_area
from [PROPERTIES] t join (select [COMP_NUMBER] , sum([AREA]) as sum_area from [PROPERTIES] group by [COMP_NUMBER]) a
on t.COMP_NUMBER = a.COMP_NUMBER

the query looks fine. whats the error you got? you can use alias directly instead of tablename

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources