SQL Multi-Table COUNT/JOIN Help Write an SQL query that will show the number of
ID: 3671629 • Letter: S
Question
SQL Multi-Table COUNT/JOIN Help
Write an SQL query that will show the number of services performed for each customer and the total cost of those services. You will need information from the Customers, ServiceAppointments, AppointmentServices, and Services tables. The output should be sorted by customer last name. Here is what the output should look like (make sure column headers are correct!):
FirstName
LastName
# services performed
Total Cost
Jeff
Allen
1
14.95
Pradeep
Boglovits
2
34.90
Matt
Crisp
3
64.85
Derek
Curlis
1
119.95
Ron
Naik
2
44.90
Ana
Prager
1
29.95
Dana
Roman
1
79.95
Shannon
Schimelman
2
109.90
Sherry
Traylor
1
39.95
Melanie
Westrick
2
49.90
FROM THE DATABASE BELOW
FirstName
LastName
# services performed
Total Cost
Jeff
Allen
1
14.95
Pradeep
Boglovits
2
34.90
Matt
Crisp
3
64.85
Derek
Curlis
1
119.95
Ron
Naik
2
44.90
Ana
Prager
1
29.95
Dana
Roman
1
79.95
Shannon
Schimelman
2
109.90
Sherry
Traylor
1
39.95
Melanie
Westrick
2
49.90
Explanation / Answer
SELECT c.FirstName,c.LastName,count(s.ServiceID) AS '# services performed',sum(s.cost) AS 'Total Cost'
FROM Customers c, Services s, AppointmentServices as, ServiceAppointments sa
WHERE c.ID = sa.CustomerID AND sa.AppointmentID = as.AppointmentID AND as.ServiceID = s.ServiceID
GROUP BY c.ID ORDER BY c.LastName;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.