Calculating total cost of booked courses in SQL

Courses
1

How can I compute the overall cost of booked courses by joining the course and booking tables, multiplying course cost by booking count?

2

Hey there, ExploringStars! :wave:

Ooh, that’s an interesting question about calculating course costs. I’m pretty curious about how you’re setting up your database. Are you working on some kind of online learning platform?

I’ve played around with similar stuff before, and I found that using a subquery can sometimes make things clearer. Have you tried something like this?

SELECT 
    c.course_id,
    c.course_name,
    c.course_cost * b.booking_count AS total_cost
FROM 
    Courses c
JOIN 
    (SELECT course_id, COUNT(*) as booking_count 
     FROM Bookings 
     GROUP BY course_id) b
ON c.course_id = b.course_id;

This way, you’re pre-counting the bookings before joining, which might be easier to wrap your head around.

What kind of data volume are you dealing with? I’m wondering if performance might be an issue with larger datasets. Have you considered using any indexes to speed things up?

Also, just curious - are you factoring in any discounts or special pricing? That could add a whole new layer of complexity to the calculation!

Let me know how it goes or if you want to bounce around any other ideas. Always fun to geek out over SQL queries! :smile:

3

To calculate the total cost of booked courses, you can use a JOIN operation combined with aggregation functions. Here’s an efficient query that should work:

SELECT 
    c.course_id,
    c.course_name,
    SUM(c.course_cost) * COUNT(DISTINCT b.booking_id) AS total_cost
FROM 
    Courses c
INNER JOIN 
    Bookings b ON c.course_id = b.course_id
GROUP BY 
    c.course_id, c.course_name;

This query joins the Courses and Bookings tables and calculates the total cost by multiplying the course cost by the number of unique bookings. The DISTINCT keyword in the COUNT function ensures that duplicate booking entries are not counted twice.

Ensure that your tables are properly indexed on the course_id columns for optimal performance, especially when dealing with large volumes of data. If you have additional requirements or need further clarification, please feel free to ask.

4

hey exploringstars, here’s a quick solution:

SELECT c.course_id, c.course_name, SUM(c.cost * COUNT(b.booking_id)) AS total_cost
FROM Courses c
JOIN Bookings b ON c.course_id = b.course_id
GROUP BY c.course_id, c.course_name;

this should give u what ur looking for. let me kno if u need more help!