Hi (Sorry, I think I posted this in the wrong spot!)
I have a report that looks like the following:
SELECT DISTINCT LandAssets_classificationsdec18.GROUP_ID, LandGroup_Attributes.LANDNAME, LandAssets_classificationsdec18.ASSET_ID, LandAssets_classificationsdec18.PID, LandAssets_classificationsdec18.SHAPE_Area, LandAssets_classificationsdec18.CLASSIFICATION, LandAssets_classificationsdec18.CLASSHIERARCHY, LandAssets_classificationsdec18.OWNER, streetsdec17.GSA_NAME
FROM ((LandAssets_classificationsdec18 LEFT JOIN civicdec17 ON LandAssets_classificationsdec18.PID = civicdec17.PID) LEFT JOIN streetsdec17 ON civicdec17.STR_CODE = streetsdec17.STR_CODE) LEFT JOIN LandGroup_Attributes ON LandAssets_classificationsdec18.GROUP_ID = LandGroup_Attributes.GROUP_ID
ORDER BY LandAssets_classificationsdec18.GROUP_ID;
The PIDs refer to parcels of land. In the report, I list all the PIDs that relate to a Classification, and the Shape_Area of that PID (or parcel). I also sum up the Shape_Areas to get a total area for each Classification. (The report looks like below)
Classification: CEMETERY
Group_ID 418
Community Location PID Area (sq.m)
00019273 6702
HALIFAX 40823999 13902
HALIFAX 41030701 67686 Total Land Area for 'GROUP_ID' = 418 (3 parcel polygons) 21.8 Acres
My problem is, because of the join to the civicdec17 table, and streetdec17 table. Because of issues with the data in these two table, of which I have no control, I sometimes get a parcel (or PID) related to a bunch of streets and communities (GSA_NAME), so the PID shows up several times, and the area gets summed up for each of those records, giving a false total area. (See below)
Group_ID 205
Community Location PID Area (sq.m)
BEDFORD 00359034 23232
COLE HARBOUR 00359034 23232
DARTMOUTH 00359034 23232
ELMSVALE 00359034 23232
FALL RIVER 00359034 23232
GLEN MARGARET 00359034 23232
HALIFAX 00359034 23232
HARRIETSFIELD 00359034 23232
HEAD OF CHEZZETCOOK 00359034 23232
MCNABS ISLAND 00359034 23232
MOOSELAND 00359034 23232
SACKVILLE 00359034 23232
SHEARWATER 00359034 23232
Total Land Area for 'GROUP_ID' = 205 (13 parcel polygons) 74.6 Acres
What I want to do, therefore, is to sum the SHAPE_AREA on a "unique" value. (I can't use the PID as the unique search criteria because of other reasons).
Can anyone help??
Thanks!!
TP
I have a report that looks like the following:
SELECT DISTINCT LandAssets_classificationsdec18.GROUP_ID, LandGroup_Attributes.LANDNAME, LandAssets_classificationsdec18.ASSET_ID, LandAssets_classificationsdec18.PID, LandAssets_classificationsdec18.SHAPE_Area, LandAssets_classificationsdec18.CLASSIFICATION, LandAssets_classificationsdec18.CLASSHIERARCHY, LandAssets_classificationsdec18.OWNER, streetsdec17.GSA_NAME
FROM ((LandAssets_classificationsdec18 LEFT JOIN civicdec17 ON LandAssets_classificationsdec18.PID = civicdec17.PID) LEFT JOIN streetsdec17 ON civicdec17.STR_CODE = streetsdec17.STR_CODE) LEFT JOIN LandGroup_Attributes ON LandAssets_classificationsdec18.GROUP_ID = LandGroup_Attributes.GROUP_ID
ORDER BY LandAssets_classificationsdec18.GROUP_ID;
The PIDs refer to parcels of land. In the report, I list all the PIDs that relate to a Classification, and the Shape_Area of that PID (or parcel). I also sum up the Shape_Areas to get a total area for each Classification. (The report looks like below)
Classification: CEMETERY
Group_ID 418
Community Location PID Area (sq.m)
00019273 6702
HALIFAX 40823999 13902
HALIFAX 41030701 67686 Total Land Area for 'GROUP_ID' = 418 (3 parcel polygons) 21.8 Acres
My problem is, because of the join to the civicdec17 table, and streetdec17 table. Because of issues with the data in these two table, of which I have no control, I sometimes get a parcel (or PID) related to a bunch of streets and communities (GSA_NAME), so the PID shows up several times, and the area gets summed up for each of those records, giving a false total area. (See below)
Group_ID 205
Community Location PID Area (sq.m)
BEDFORD 00359034 23232
COLE HARBOUR 00359034 23232
DARTMOUTH 00359034 23232
ELMSVALE 00359034 23232
FALL RIVER 00359034 23232
GLEN MARGARET 00359034 23232
HALIFAX 00359034 23232
HARRIETSFIELD 00359034 23232
HEAD OF CHEZZETCOOK 00359034 23232
MCNABS ISLAND 00359034 23232
MOOSELAND 00359034 23232
SACKVILLE 00359034 23232
SHEARWATER 00359034 23232
Total Land Area for 'GROUP_ID' = 205 (13 parcel polygons) 74.6 Acres
What I want to do, therefore, is to sum the SHAPE_AREA on a "unique" value. (I can't use the PID as the unique search criteria because of other reasons).
Can anyone help??
Thanks!!
TP
