I’m currently trying to complete the Database Fundamentals course, and with a combination of trial-and-error and Stack Overflow have been getting through the Intermediate Queries. However, I’m completely stuck on the HAVING clauses question.
I’ve been asked to do the following:
For these two projects, Central Valley Hospital project and Big Money Bank project, return:
- The project’s name, as “Project Name”
- The first name and last name of employees who worked on the project, as “First Name” and “Last Name”, and
- Their total spending (i.e. the cost of their job orders), as “Spending”.
Filter your results to employees who spent over $10,000,000 on either project, and order your results by highest cost to lowest.
After a number of attempts that produced error message, I have produced the following query that, although not returning an error, doesn’t return any data:
SELECT p.name AS 'Project Name', e.first_name AS 'First Name', e.last_name AS 'Last Name', SUM(jo.quantity) * SUM(jo.price) AS 'Spending' FROM projects p JOIN job_orders jo ON p.id = jo.project_id JOIN employees e ON p.id = e.id WHERE p.name = 'Central Valley Hospital' OR p.name = 'Big Money Bank' GROUP BY 1, 2, 3 HAVING 'Spending' > 10000000 ORDER BY 4 DESC;
The Expected Results are supposed to display seven rows with an assorted range of spending figures. Has anyone completed this question and able to explain what I’m doing wrong?