Results 1 to 3 of 3
  1. #1
    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. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    You can do it for yourself.

    This task is for elementary school.

  3. #3
    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
  •