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

1. Registered User
Join Date
Nov 2010
Location
London, UK
Posts
7

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

2. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
sum(extraprice)

Dave

3. Registered User
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 ???

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
use a group by clause
or limit using a where clause

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

6. Registered User
Join Date
Nov 2010
Location
London, UK
Posts
7
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. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
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. Registered User
Join Date
Nov 2010
Location
London, UK
Posts
7
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
•