|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
This should be simple, so I was surprised when I got the wrong number back. Basically, here is my table design (simplified):
--tblProjects-- ProjectID --tblPersonnel-- PersonnelID ProjectID Quantity UnitPrice --tblEquipment-- EquipmentID ProjectID Quantity UnitPrice I've set up relationships between the two three tables (projects to personnel and projects to equipment) as a one to many. That works fine. However I want to make a view that returns all the information from projects, and then the sum of all the Quantity * Price's of both Personnel and Equipment. Using the Query Builder, it made something like this: SELECT vwProjects.ProjectID, ISNULL(SUM(vwPersonnel.Quantity * vwPersonnel.UnitPrice), 0) + ISNULL(SUM(vwEquipment.Quantity * vwEquipment.UnitPrice), 0) as [Amount] FROM vwProjects LEFT OUTER JOIN vwPersonnel ON vwProjects.ProjectID = vwPersonnel.ProjectID LEFT OUTER JOIN vwEquipment ON vwProjects.ProjectID = vwEquipment.ProjectID However, lets say I have two related records in Personnel and one in Equipment. The first record (if I broke up the records instead of summing them up), would have the product of the first Personnel and the Product of the first Equipment. The second row would have the product of the second Personnel, AND the product of the first Equipment again. I understand why this is happening, but what changes do I need to make to fix it? I hope this wasn't too confusing. Thanks for any help! |
|
#2
|
|||
|
|||
|
Currently, I'm just going to use a user-defined function to handle this, although it seems like a lot of extra DB stress if it's not needed.
|
|
#3
|
|||
|
|||
|
Code:
select vwProjects.ProjectID, (SELECT coalesce(SUM(vwPersonnel.Quantity * vwPersonnel.UnitPrice), 0) FROM vwPersonnel where vwProjects.ProjectID = vwPersonnel.ProjectID ) + (select coalesce(SUM(vwEquipment.Quantity * vwEquipment.UnitPrice), 0) from vwEquipment where vwProjects.ProjectID = vwEquipment.ProjectID) as amount from wwProjects Since equipment and personell are unrelated you shouldn't join them. |
![]() |
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Getting sum from several related fields |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|