Join multiple tables and sum()

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 2000 Forums

Transact-SQL (2000)

Join multiple tables and sum()

Author

Topic

Mesktomten
Starting Member

8 Posts

Posted - 2006-02-01 : 07:09:52

In my companys database I have a calendar table, orders table and delivery table.

In the calendar table I've got the columns DATE, WEEK and DAY (EXamlpe 2006-01-01, 200552, SUNDAY)

If I join this one with the orders table to get the sum of order per week it works just fine, but when I try to join both the orders and delivery tables it, for some reason, gives me strange figures...

For example:

SELECT
C.Week
, sum(O.quantity)
FROM calendar C
INNER JOIN
orders O
ON C.date = O.date
WHERE O.ProductID = 637
AND C.week > 200551
GROUP BY C.week
ORDER BY C.week

Gives me this result:

WEEK ORDERS
---- ------
200552 103557
200601 92027
200602 103328

And this query, with both tables joned:

SELECT
C.week
, sum(O.quantity)
, sum(D.quantity)
FROM calendar C
INNER JOIN
orders O
ON C.date = O.date
INNER JOIN
delivery D
ON C.date = D.date
AND O.productID = D.productID
WHERE O.productID = 637
AND C.week > 200551
GROUP BY C.week
ORDER BY C.week

Gives me this result:

WEEK ORDERS DELIVERY
---- ------ --------
200552 24620720 120678704
200601 31855710 163286814
200602 36715562 200013250

What am I doing wrong..?

I would really appreciate some help on this one...

/Martin

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-01 : 07:51:53

Do you have ProductID in delivery? If so then what does a join between calendar and delivery give you?

It seems like the records between delivery and orders is a many to many relationship and you are bringing out the same order many times..

Mesktomten
Starting Member

8 Posts

Posted - 2006-02-01 : 07:56:01

Ahh... Thanks!

I think I know what the cause of error is now! The remaining problem is to make it dissapear...

Mesktomten
Starting Member

8 Posts

Posted - 2006-02-01 : 07:59:27

Sorry, I didn't give you the answer to the first question!

Yes, I have a ProductID column in both tables... The relationship I want to get is what has been ordered and what we were able to deliver...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources