Results 1 to 10 of 10

Thread: db2

  1. #1
    Join Date
    Mar 2009
    Posts
    8

    Unanswered: db2

    hello i am trying to find the sum of one column from table A and subtract it from a value of table B...how this can be done

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    b.value_x - SUM(a.column_y)

    or

    b.value_x - ( SUM(a.column_y) OVER(PARTITION BY ... ) )

    depending on your requirement(sum over what group?).
    Last edited by tonkuma; 03-31-09 at 05:32.

  3. #3
    Join Date
    Mar 2009
    Posts
    8
    actualy i'm a beginer in DB2 and i dont know how to compare those statements my actual problem is a booking flights annd im trying to find out the available seats in the table flights i have FlightID , FlightDate, Origin,Destination, MaxCapacity and in the other table te table flightbooking i have BookingID,CustomerID,FlightID,NumSeats,Status and BookingTime and i have to sum the numseats and subtruct from the max capacity.

    But i cant find how to do the equation with alla the select statments... i mean to select...from...
    select...from... where Flight.FLIGHTID = Flightbooking.Flightid
    ...and then i dont know how to complete that...can you help me??

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example.

    Code:
    SELECT
           flt.FlightDate
         , flt.FlightID
         , MaxCapacity
         , MAX(Origin)      AS Origin
         , MAX(Destination) AS Destination
         , MaxCapacity - SUM(NumSeats) AS available_seats
         , COUNT(DISTINCT CustomerID) AS number_of_customer
      FROM
           flight        flt
      LEFT OUTER JOIN
           flightbooking fbk
       ON  flt.Flightid   = fbk.Flightid
       AND flt.FlightDate = fbk.FlightDate
     GROUP BY
           flt.FlightDate
         , flt.Flightid
         , MaxCapacity
    ;
    Last edited by tonkuma; 03-31-09 at 06:28.

  5. #5
    Join Date
    Mar 2009
    Posts
    8
    thanks very match is working=D

  6. #6
    Join Date
    Mar 2009
    Posts
    8
    can i ask you something more??
    ------------------------------ Commands Entered ------------------------------
    SELECT DISTINCT
    flightBooking.FlightID,
    Customer.CustomerID,
    Customer.NAME
    FROM
    Customer
    LEFT OUTER JOIN
    flightbooking
    ON flightbooking.flightid = 100;
    ------------------------------------------------------------------------------
    SELECT DISTINCT flightBooking.FlightID, Customer.CustomerID, Customer.NAME FROM Customer LEFT OUTER JOIN flightbooking ON flightbooking.flightid = 100

    FLIGHTID CUSTOMERID NAME
    ----------- ----------- --------------------
    100 1 Andrew Brown
    100 2 Andriani Papakwsta
    100 3 Pra3ulla Theodoulou

    3 record(s) selected.



    here i'm trying to generate a list with the customer who are going to travel in a specific flight (100) but the result is a list with all the customers

    i tried and..... ON flightbooking.status = 'r';
    which mean that they are Reserved but i have the same problem

    can you help me?

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Why are you using outer join?
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Mar 2009
    Posts
    8
    because i'm using the flightid the customerid and the customer name

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    OK then, do you know the difference between inner and outer joins? If not, I suggest you find some SQL guide for beginners and read it before you ruin your employer's reputation.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Mar 2009
    Posts
    8
    that helps a lot thanks

Posting Permissions

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