Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Standard SQL Functions

Introduction
Function COUNT
Function AVG
Functions SUM, MAX, MIN
Review

Instruction

Thank you! Now we'll show you a common mistake: it's most often wrong to use LEFT JOIN with COUNT(*).

Let's analyze the following problem: we want to show each client's name together with the number of projects they have commissioned. You may be tempted to write the following:

SELECT
  name,
  COUNT(*)
FROM client
LEFT JOIN project
  ON client.client_id = project.client_id
GROUP BY name;

... but this query can produce incorrect results! If a client has no projects, COUNT(*) will give the result of 1 (because the row with the client name itself is one row indeed) instead of 0. In such queries, you need to use count with a column name:

SELECT
  name,
  COUNT(project_id)
FROM client
LEFT JOIN project
  ON client.client_id = project.client_id
GROUP BY name;

Now, the query will return 0 if there are no projects commissioned by a given client.

Exercise

Show the first and last name of each translator together with the number of projects they have completed (0 if there are no such projects). Name the last column projects_no.

Stuck? Here's a hint!

Join the tables translator and project. Group all the rows by the columns first_name and last_name. Use LEFT JOIN to also select those translators who didn't complete any project.