General information
Category:
Coding, syntax & commands
>All,
>
>I am writing a building inspection application.
>
>Each DISTICT has one or more BUILDINGS
>Each BUILDING has one or more ADDITIONS
>Each ADDITION has one or more INSPECTIONS
>Each INSPECTION has one or more INSPECTIONCOMPONENT
>Each INSPECTIONCOMPONENT has one or more INSPECTIONCOMPONENTITEM
>Each ITEM has one or more INSPECTIONCOMPONENTITEM
>
>I need to sum the extension of the INSPECTIONCOMPONENTITEM quantity and the ITEM unit cost for a specific building using the following:
>
>SELECT ;
> SUM(SFAInspectionComponentItem.iQuantity * SFAItem.yUnitCost) AS iTotal;
>FROM ;
> sfa!sfainspectioncomponent ;
> INNER JOIN sfa!sfainspectioncomponentitem;
> INNER JOIN sfa!sfaitem;
> INNER JOIN sfa!sfainspectioncomponent;
> INNER JOIN sfa!sfaaddition;
> INNER JOIN sfa!sfabuilding ;
> ON Sfabuilding.iid = Sfaaddition.ibldgid ;
> ON Sfaaddition.iid = Sfainspection.iadditionid ;
> ON Sfainspection.iid = Sfainspectioncomponent.iinspectionid ;
> ON Sfainspectioncomponent.iid = Sfainspectioncomponentitem.iinspectioncomponentid;
> ON Sfaitem.iid = Sfainspectioncomponentitem.icomponentitemid ;
>WHERE Sfabuilding.iid = ?vp_iBuildingId ;
>INTO CURSOR REX
>
>Unfortunately the total is double what it should be. In my example there are 2 COMPONENTS for the INSPECTION and this seems to lead to a complete duplication of all the amounts.
>
>Since I am using a SUM, I cannot use DISTINCT is the SQL.
>
>How can I get the data not to duplicate?
>
>TIA
>
>Rex
use the GROUP BY
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only