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

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 could 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.

Stuck? Here's a hint!

Join the tables translator and project. Group all the rows by the columns first_name and last_name.

Console

Code editor

Result

TableConsole