Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2006
    Posts
    8

    Unanswered: Table optimization

    I have a table generated by

    Code:
    SELECT FlightID, NumSeats, Status FROM FlightBooking ORDER BY FlightID
    Which gives me the result

    FlightID | NumSeats | Status
    -----------------------------------
    1 | 2 | R
    2 | 4 | H
    2 | 2 | R
    3 | 3 | H
    4 | 2 | R
    4 | 4 | H
    4 | 1 | R
    5 | 1 | R

    I'd like to combine these records so it displays only one row with 4, 3, R

    And also If i want to insert a row (e.g. 1, 2, R) it will be added to existing the row (to give 1, 4, R)

    Many thanks,

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    to combine your rows in your query results,
    Code:
    SELECT FlightID
         , sum(NumSeats) as total_NumSeats
         , Status 
      FROM FlightBooking 
    GROUP BY FlightID, Status 
    ORDER BY FlightID
    to "add" a row to an existing row, don't use INSERT, use UPDATE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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