SELECT INNER JOIN SUM - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT INNER JOIN SUM

Author

Topic

marly
Starting Member

6 Posts

Posted - 2008-06-14 : 18:39:13

I have three tables.

Quote
QuoteID, QuoteNumber,

Transportation

TransportationID, QuoteID, Item, Description, Cost

OptionalCharges

OptionalChargesID, QuoteID, Item, Description, Cost

What I want to do is SUM(Cost) for the Transportation and the Optional Charges table. If I do a normal INNER JOIN, with the SUM for Cost on the table Transportation and Optional Charges, it will SUM each twice. For example if Transportation has three rows with a cost of 10, 20, 30, it will SUM a total of 120 instead of 60.

So I came up with:

Select
Quote.QuoteID, QuoteDate, t.TransportationTotalCost

FROM
Quote.Quote
INNER JOIN
(

SELECT
QuoteID, SUM(COST) AS TransportationTotalCost
FROM
Quote.Transportation
GROUP BY
QuoteID
) t
on Quote.QuoteID = t.QuoteID
WHERE Quote.QuoteID = 135

Which gives me the total for the Transportation Table, but how do I go about adding in the Optional Charges Table?

Thanks,

marly

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-15 : 02:48:41

Try this one

Select
Quote.QuoteID, QuoteDate, t.TransportationTotalCost

FROM
Quote.Quote
INNER JOIN
(

SELECT
QuoteID, SUM(Transportation.COST) AS TransportationTotalCost,
SUM(OptionalCharges.COST) AS OptionalChargesTotalCost
FROM
Transportation
INNER JOIN OptionalCharges
ON Transportation.QuoteID=OptionalCharges.QuoteID
GROUP BY
Transportation.QuoteID,OptionalCharges.QuoteID
) t
on Quote.QuoteID = t.QuoteID
WHERE Quote.QuoteID = 135

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-15 : 03:08:56

You could probably post some sample data from the tables to give us an idea of how the tables are related as we dont know if relation is 1 to 1 or 1 to many?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources