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

szuwarencja · #1 (**permalink**) 11-09-10, 06:39

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 !!

dav1mo · #2 (**permalink**) 11-09-10, 09:22

sum(extraprice)

Dave

szuwarencja · #3 (**permalink**) 11-09-10, 10:59

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 ???

healdem · #4 (**permalink**) 11-09-10, 11:14

use a group by clause
or limit using a where clause

dav1mo · #5 (**permalink**) 11-09-10, 14:01

Code:

select rental.RentalPK 
      ,SUM (extraprice)
  from ..
where...
group by rental.RentalPK

szuwarencja · #6 (**permalink**) 11-09-10, 14:19

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 !!