Calculating total cost of booked courses in SQL

Courses
1 
Hey everyone! I'm trying to figure out how to get the total cost of currently booked courses using SQL. Here's what I've got:

A Course table with CourseId and cost columns:
CourseId | cost
---------|-----
1        | 250
2        | 350
3        | 450
4        | 550

And a Booking table with BookingId and CourseId:
BookingId | CourseId
----------|----------
101       | 1
102       | 1
103       | 2
104       | 4

I want to get a result like this:
CourseId | TotalCost
---------|----------
1        | 500
2        | 350
4        | 550

I know I need to use a JOIN, but I'm not sure how to multiply the number of bookings by the course cost for each CourseId. Any help would be awesome!
2

hey nate, try this:

SELECT c.CourseId, COUNT(b.BookingId) * c.cost AS TotalCost
FROM Course c
JOIN Booking b ON c.CourseId = b.CourseId
GROUP BY c.CourseId, c.cost

this joins the tables, counts bookings, multiplies by cost, and groups by course. should give you what u need!

3

Hey there, Nate! :wave:

Looks like you’ve got an interesting SQL puzzle on your hands. I’m curious, have you tried any queries yet? Sometimes it helps to start with something simple and build from there.

Here’s a thought - what if we approach this from the Booking table’s perspective? We could do something like:

SELECT b.CourseId, COUNT(*) * c.cost AS TotalCost
FROM Booking b
JOIN Course c ON b.CourseId = c.CourseId
GROUP BY b.CourseId, c.cost

This way, we’re counting the bookings first and then multiplying by the course cost. It might give a different perspective on the problem.

By the way, are you working on a real-world project or is this more of a learning exercise? I’m always interested in hearing about how people are applying SQL in different contexts.

Let me know if this helps or if you want to brainstorm some more ideas!

4

I’ve encountered a similar challenge in the past. Here’s a solution that should work for you:

SELECT c.CourseId, SUM(c.cost) AS TotalCost
FROM Course c
INNER JOIN Booking b ON c.CourseId = b.CourseId
GROUP BY c.CourseId

This query joins the Course and Booking tables, then sums up the cost for each course that has bookings. The INNER JOIN ensures we only get courses with actual bookings.

One thing to note: if you have courses without any bookings and you want those included with a TotalCost of 0, you’d need to use a LEFT JOIN instead of INNER JOIN.

Hope this helps solve your problem!