Hugo Zink Hugo Zink - 1 year ago 95
MySQL Question

SUM on a left joined column is limiting results

I have the following SQL query for a few testing tables:

SELECT salesperson.spnum, salesperson.spname, sales.quantity
FROM salesperson
LEFT JOIN sales ON sales.spnum = salesperson.spnum;

This returns the following results:

spnum spname quantity
1 "Bob Jansen" 5
1 "Bob Jansen" 10
1 "Bob Jansen" 1600
2 "Henk de Vries" NULL
3 "Anne de Graaf" NULL

However, when I use a
for the select:

SELECT salesperson.spnum, salesperson.spname, SUM(sales.quantity) AS quantity
FROM salesperson
LEFT JOIN sales ON sales.spnum = salesperson.spnum;

I only get results from rows where the
column is not null:

spnum spname quantity
1 "Bob Jansen" 1615

How do I prevent
from limiting my results like this? I would like to get either NULL or "0" as quantity.

Answer Source

You are misinterpreting what is happening.

When you add a SUM() to your original query, you are turning the query into an aggregation query. An aggregation query with no GROUP BY always returns exactly one row (even if the original tables have no rows). The SUM() is the SUM() of all columns.

In most databases, your code would return an error, because there are columns in the SELECT that are not in the GROUP BY. MySQL does support this syntax -- although my advice is basically to never use it.

The solution is simple: Add the appropriate GROUP BY clause:

SELECT sp.spnum, sp.spname, SUM(s.quantity) AS quantity
FROM salesperson sp LEFT JOIN
     sales s
     ON s.spnum = sp.spnum
GROUP BY sp.spnum, sp.spname;

Notice that I added table aliases to your query. This makes the query easier to write and to read.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download