1. Registered User
Join Date
Mar 2009
Posts
8

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. Registered User
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 04:32.

3. Registered User
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. Registered User
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 05:28.

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

6. Registered User
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. :-)
Join Date
Jun 2003
Location
Posts
5,516
Why are you using outer join?

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

9. :-)
Join Date
Jun 2003
Location
Posts
5,516
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.

10. Registered User
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
•