Calculating total activity expenses per parent in SQL

Courses
1

Hey folks, I’m stuck on a SQL problem. I need to figure out how much each parent is spending on their kid’s activities. Each activity costs $5.50.

I want my query to show:

  1. Parent’s full name
  2. Number of activities
  3. Total cost

Here’s what I’ve got so far:

SELECT 
    CONCAT(P.parent_title, ' ', P.parent_fname, ' ', P.parent_sname) AS ParentName,
    COUNT(CA.activity_id) AS ActivityCount
FROM 
    Parents P
    JOIN Children C ON P.parent_id = C.parent_id
    JOIN ChildActivities CA ON C.child_id = CA.child_id
GROUP BY 
    ParentName
ORDER BY 
    P.parent_sname, P.parent_fname

This gives me the parent’s name and activity count, but I can’t figure out how to add the total cost. Any ideas on how to multiply the activity count by 5.50 and include it in the results? Thanks!

2

Hey there ExploringOcean! :wave:

I totally get your frustration with SQL sometimes. But don’t worry, we’re gonna crack this one together!

So you’re super close with your query. The trick is to use a little math in your SELECT statement. Have you tried something like this?

SELECT 
    CONCAT(P.parent_title, ' ', P.parent_fname, ' ', P.parent_sname) AS ParentName,
    COUNT(CA.activity_id) AS ActivityCount,
    COUNT(CA.activity_id) * 5.50 AS TotalCost
FROM 
    Parents P
    JOIN Children C ON P.parent_id = C.parent_id
    JOIN ChildActivities CA ON C.child_id = CA.child_id
GROUP BY 
    ParentName
ORDER BY 
    P.parent_sname, P.parent_fname

See what I did there? Just added another column that multiplies the activity count by 5.50. Easy peasy!

But here’s a question for you - what if the activity costs change in the future? Have you considered storing the price in a separate table? That way, you could join it in and make your query more flexible. Just a thought!

Let me know if this helps or if you need any more pointers. SQL can be tricky, but it’s also kinda fun once you get the hang of it, right? :blush:

3

I’ve encountered similar challenges when working with SQL queries. Your approach is on the right track. To include the total cost, you can simply multiply the COUNT function by the activity cost within the SELECT statement. Here’s how you can modify your query:

SELECT 
    CONCAT(P.parent_title, ' ', P.parent_fname, ' ', P.parent_sname) AS ParentName,
    COUNT(CA.activity_id) AS ActivityCount,
    COUNT(CA.activity_id) * 5.50 AS TotalCost
FROM 
    Parents P
    JOIN Children C ON P.parent_id = C.parent_id
    JOIN ChildActivities CA ON C.child_id = CA.child_id
GROUP BY 
    P.parent_id, P.parent_title, P.parent_fname, P.parent_sname
ORDER BY 
    P.parent_sname, P.parent_fname

This should give you the desired output. One thing to consider is storing activity costs in a separate table for better flexibility if prices change in the future. Let me know if you need any clarification on this solution.