Problem with Intermediate Queries - HAVING Clauses

Hi everyone,

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?

Thanks!

Hi @the-bystander!

Take a look at your second JOIN clause and the HAVING clause.

An example of a working query.
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 jo.employee_id = e.id 
WHERE  p.name IN ( 'Big Money Bank', 'Central Valley Hospital' )
GROUP BY  1, 2, 3
HAVING Spending > 10000000
ORDER BY  4 DESC;

Let us know if you have any more questions. Great job with getting so far in the course! You are almost done!

1 Like

Hi @MelissaFontaine, this query worked so thank you very much!

Can you please provide me with some additional explanation about parts of your query?

  • How do you determine which datasets to join? I had joined employees and projects together and that did not work - is it because they are not directly linked and so they must be linked either to job_orders or projects_employees?

  • When can you refer to aliases without the single quotes around them? I had tried variations of the query before posting here, but as my HAVING section always had ‘Spending’ with single quotes no data was returning. I don’t recall this exception being noted in the lessons, and other documentation I was reading didn’t appear to note this either.

Again, thanks for solving this problem - it was getting very frustrating!

@the-bystander, I’m glad the query worked! Here are answers to your follow-up questions:

  • Yes, they must be joined via the job_orders table since they don’t link directly to each other. They can technically also be joined via project_employees, but that wouldn’t be useful for this situation since it’s asking about employees’ job orders.

  • The ability to use column aliases in a HAVING clause is a MySQL thing (not a SQL standard) - you can find out more here. Essentially, for MySQL, anything within single or double quotes are strings (e.g. “Spending” ), anything that isn’t in quotes is considered a column alias (e.g. Spending ). If a column’s alias has a space between words and you want to refer to the alias, use backticks (e.g. `First Name`).

Good luck with the rest of the course!

2 Likes