Use HAVING for Aggregate Filters, Not WHERE

Use HAVING for Aggregate Filters, Not WHERE

When filtering on aggregated columns like COUNT or SUM, WHERE won’t work—you need HAVING. The difference tripped me up when I needed to find projects with zero active tasks.

Here’s the wrong approach that throws a syntax error:

-- This FAILS with syntax error
SELECT 
    p.id, 
    p.title,
    COUNT(t.id) as task_count
FROM projects p
LEFT JOIN tasks t ON t.project_id = p.id 
WHERE t.status = 'active'
  AND COUNT(t.id) = 0  -- ERROR: Invalid use of aggregate
GROUP BY p.id, p.title

MySQL will complain: “Invalid use of group function.” You can’t filter on aggregates in the WHERE clause because aggregation happens after the WHERE filter is applied.

The correct approach uses HAVING:

SELECT 
    p.id, 
    p.title,
    COUNT(t.id) as task_count
FROM projects p
LEFT JOIN tasks t ON t.project_id = p.id 
    AND t.status = 'active'
GROUP BY p.id, p.title
HAVING task_count = 0

The key difference: WHERE filters rows before aggregation, HAVING filters groups after. Using WHERE on an aggregate throws a syntax error.

In Laravel’s query builder, this translates to:

DB::table('projects')
    ->leftJoin('tasks', function ($join) {
        $join->on('tasks.project_id', '=', 'projects.id')
             ->where('tasks.status', '=', 'active');
    })
    ->select('projects.id', 'projects.title', DB::raw('COUNT(tasks.id) as task_count'))
    ->groupBy('projects.id', 'projects.title')
    ->havingRaw('task_count = 0')
    ->get();

Or if you already aliased it in selectRaw, you can use the cleaner having() method:

->having('task_count', 0)

Understanding this distinction prevents hours of debugging cryptic MySQL errors. Remember: filter rows with WHERE, filter aggregates with HAVING.