 <p>Here's a trick: calculating a <code>SUM()</code> of values that are known to be either 1 or 0 is equivalent to a <code>COUNT()</code> of the rows where the value is 1. And you know that a boolean comparison returns 1 or 0 (or NULL).</p> <pre><code>SELECT c.catname, COUNT(m.catid) AS item_count, SUM(i.ownerid = @ownerid) AS owner_item_count FROM categories c LEFT JOIN map m USING (catid) LEFT JOIN items i USING (itemid) GROUP BY c.catid; </code></pre> <p>As for the bonus question, you could simply do an inner join instead of an outer join, which would mean only categories with at least one row in <code>map</code> would be returned.</p> <pre><code>SELECT c.catname, COUNT(m.catid) AS item_count, SUM(i.ownerid = @ownerid) AS owner_item_count FROM categories c INNER JOIN map m USING (catid) INNER JOIN items i USING (itemid) GROUP BY c.catid; </code></pre> <p>Here's another solution, which is not as efficient but I'll show it to explain why you got the error:</p> <pre><code>SELECT c.catname, COUNT(m.catid) AS item_count, SUM(i.ownerid = @ownerid) AS owner_item_count FROM categories c LEFT JOIN map m USING (catid) LEFT JOIN items i USING (itemid) GROUP BY c.catid HAVING item_count &gt; 0; </code></pre> <p>You can't use column aliases in the <code>WHERE</code> clause, because expressions in the <code>WHERE</code> clause are evaluated before the expressions in the select-list. In other words, the values associated with select-list expressions aren't available yet.</p> <p>You can use column aliases in the <code>GROUP BY</code>, <code>HAVING</code>, and <code>ORDER BY</code> clauses. These clauses are run after all the expressions in the select-list have been evaluated.</p>