Thread: Joining four tables and calculating sums

1. Registered User
Join Date
Apr 2013
Posts
1

Unanswered: Joining four tables and calculating sums

Hi!

I'm new to DB2 and has a query that I just can't get right

I have four tables:

t.Users
ID | Name
1 | Eric
2 | Hans
3 | Eve

t.Balance
userID | orderID| Payment
1 | 100 | 1000
2 | 200 | 100
2 | 200 |-100
1 | 100 | -50

t.Order
Userid | orderID | Sum
1 | 100 | 1000
1 | 100 | 2000

t.OrderDetails
orderID | ProductID | Size
100 | 11 | 20
100 | 12 | 15
100 | 13 | 10
From this I would like to build a query thats lists all users that has a balance thats <> 0 and then calculate total ordersum for the open orders. To end it all I would like to present the biggest and smallest size in the order.

In other words I would like this result:

Name | OrderID | Balance | Ordersum | Smallest size | Biggest size
Eric | 100 | 950 | 3000 | 10 | 20

2. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
You can do it for yourself.

This task is for elementary school.

3. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
It would be not so easy as to say "This task is for elementary school".

The simple and easy strategy "Join all 4 tables, then apply GROUP BY and HAVING" might produce inccorect results.
Note: If all payment for each order_id were different and all order_sum for each order_id were different,
this strategy(with "SUM(DISTINCT payment)" and "SUM(DISTINCT order_sum)") might be a solution.

A way might be to investigate your requirements step by step
and to construct subqueries corresponding to each step, like...

(1) lists all users that has a balance thats <> 0

Subquery(1):
Code:
```SELECT user_id
, SUM(payment) AS balance
FROM  Balance
GROUP BY
user_id
HAVING
SUM(payment) <> 0```
(2) and then calculate total ordersum for the open orders.

Subquery(2):
Code:
```SELECT o.order_id
, b.balance
, order_sum
FROM  ( <Subquery(1)> ) AS b
INNER JOIN
(SELECT user_id
, order_id
, SUM(order_sum) AS order_sum
FROM  Order
GROUP BY
user_id
, order_id
) AS o
ON   o.user_id = b.user_id```
(3) To end it all I would like to present the biggest and smallest size in the order.

In other words I would like this result:
Code:
```Name | OrderID | Balance | Ordersum | Smallest size | Biggest size
Eric |     100 |     950 |     3000 |            10 |           20```
Subquery(3):
Code:
`/* snipped */`
Last edited by tonkuma; 04-18-13 at 23:04. Reason: Add Note.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•