Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2010
    Location
    London, UK
    Posts
    7

    Red face Unanswered: Pls HELP!!!! new to sql - problem with calculating total cost :(

    Hi guys,

    need to mention - i'm new to this game, have a strong object oriented programming background but now need to catch up with sql ok, my problem:
    as a proof of concept for my uni assignment I have built a db with few tables for a online car rental company. you can rent a car with few extras like AC etc. There are 7 car groups, each with different tariff, based on the country in which the branch is. so I have a tables like: rental (RentalPK, CustomerFK, BranchFK, VehicleFK, StartDate, EndDate ...), rental_extra_item (RntlExtraPK, ExtraName, ExtraPrice), rental_all_item (RentalFK, RntlExtraFK, TariffFK).
    Question is how can I calculate the total cost of all extras in the rental order (there may be up to seven, all with different prices).
    Sorry if its all a bit too long or confusing, its my first ever post on this forum Also didn't post any actual sql code 'cos really don't have a clue where to start
    Thanks in advance for any advice people !!

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    sum(extraprice)

    Dave

  3. #3
    Join Date
    Nov 2010
    Location
    London, UK
    Posts
    7
    thanks Dave,

    I've tried that already, it comes back with sum of all extra's prices that were rented out, not specifically for the particular rental... what I need is eg. if rental.RentalPK is 1 and that rental has two extras, so in rental_all_item I would have had something like:
    RentalFK RntlExtraFK
    1 1
    1 5
    2 3
    then I need sum of prices for the first two. what SUM (extraprice)
    does it gives me back sum of all three prices (in the above example)...
    any other suggestions ???

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use a group by clause
    or limit using a where clause
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Code:
    select rental.RentalPK 
          ,SUM (extraprice)
      from ..
    where...
    group by rental.RentalPK

  6. #6
    Join Date
    Nov 2010
    Location
    London, UK
    Posts
    7

    Thumbs up

    Thank you so much guys !!!!

    after healdem's post I've came up with this

    SELECT DISTINCT a.RentalPK, SUM(DISTINCT Price)
    FROM rental a, rental_extra_item b, rental_all_item c
    WHERE a.RentalPK = c.RentalPK AND b.RntlExtraPK = c.RntlExtraPK
    GROUP BY a.RentalPK

    and it works like a charm
    looking at Dave's post I have my confirmation.
    have to say love this forum, will be annoying you guys with more rookie like questions in the near future.
    thnx again, appreciate ur help !!

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The distinct is not needed. I think most dbms's will omit it on the query rewrite, but for clarity sake you may want to remove it.
    Dave

  8. #8
    Join Date
    Nov 2010
    Location
    London, UK
    Posts
    7

    Thumbs up

    Just checked it Dave , you are correct. Think it was left over from my previous attempts. cheers !

Posting Permissions

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