Hugo Zink Hugo Zink - 3 months ago 12
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
SUM()
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
quantity
column is not null:

spnum spname quantity
1 "Bob Jansen" 1615


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

Answer

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.

Comments