MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesMS SQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old June 23rd, 2004, 02:57 PM
sn1p3t sn1p3t is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2004
Posts: 14 sn1p3t User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Question Getting sum from several related fields

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!

Reply With Quote
  #2  
Old June 23rd, 2004, 03:09 PM
sn1p3t sn1p3t is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2004
Posts: 14 sn1p3t User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
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.

Reply With Quote
  #3  
Old June 23rd, 2004, 04:49 PM
swampBoogie swampBoogie is offline
Contributing User
Dev Shed Intermediate (1500 - 1999 posts)
 
Join Date: Jan 2003
Location: Paris Uppland
Posts: 1,773 swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 2 Weeks 4 Days 14 h 30 m 5 sec
Reputation Power: 37
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.

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesMS SQL Development > Getting sum from several related fields


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway