Laravel GROUP BY and HAVING Examples

Use WHERE to filter source rows before aggregation, GROUP BY to define the result groups, and HAVING to filter those grouped results. In Laravel Query Builder or Eloquent, make the grouping columns and aggregates explicit. With MySQL's ONLY_FULL_GROUP_BY, ambiguous grouped queries are rejected unless a selected nonaggregate is grouped or MySQL can prove it is functionally dependent on the grouping key.

First define the result grain

Ask “one output row per what?” before writing the query. “One row per customer with paid revenue” tells you that customer_id is the grouping key and revenue must be aggregated.

Basic groupBy() with an aggregate

use Illuminate\Support\Facades\DB;

$ordersByStatus = DB::table('orders')
    ->select('status')
    ->selectRaw('COUNT(*) AS order_count')
    ->groupBy('status')
    ->get();

SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status;

Every output row represents one status group rather than one original order.

WHERE filters rows; HAVING filters groups

$customers = DB::table('orders')
    ->select('customer_id')
    ->selectRaw('SUM(total) AS paid_revenue')
    ->where('status', 'paid')
    ->groupBy('customer_id')
    ->having('paid_revenue', '>=', 1000)
    ->get();

SELECT customer_id, SUM(total) AS paid_revenue
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING paid_revenue >= 1000;

The WHERE clause removes unpaid rows before SUM(); HAVING then removes groups whose aggregate is below the threshold.

Why an aggregate alias belongs in HAVING, not WHERE

// Incorrect: revenue is not available to WHERE.
DB::table('orders')
    ->select('customer_id')
    ->selectRaw('SUM(total) AS revenue')
    ->where('revenue', '>', 1000)
    ->groupBy('customer_id')
    ->get();

MySQL permits select aliases in HAVING but not in WHERE because of the logical evaluation stage at which the alias becomes available.

havingRaw(): bind values

$minimumRevenue = 2500;

$rows = DB::table('orders')
    ->select('department')
    ->selectRaw('SUM(price) AS total_sales')
    ->groupBy('department')
    ->havingRaw('SUM(price) > ?', [$minimumRevenue])
    ->get();

Laravel documents bindings as the second argument to havingRaw(). Do not concatenate request values into raw SQL. Dynamic identifiers such as requested column names cannot be value-bound, so allowlist them.

ONLY_FULL_GROUP_BY protects against ambiguous output

SELECT customer_id, id, SUM(total) AS revenue
FROM orders
GROUP BY customer_id;

If a customer has multiple orders, which id should represent the group? With ONLY_FULL_GROUP_BY, MySQL rejects nonaggregated selected expressions that are neither grouped nor functionally dependent on grouped expressions.

Functional dependency is the important nuance

The rule is not simply “every selected nonaggregate must literally appear in GROUP BY.” MySQL recognizes functional dependency in valid cases, such as columns dependent on a grouped primary key. Do not add redundant grouping columns merely to silence an error without understanding the output grain.

Do not disable strict grouping as the first fix

Usually the correct fix is to remove a column that does not belong at the grouped grain, aggregate it intentionally, group at a more detailed grain, or use a deterministic subquery/window technique for the specific row you need. ANY_VALUE() is for cases where nondeterminism is intentionally acceptable, not a universal bandage.

Group by multiple columns

$summary = DB::table('orders')
    ->select('status', 'currency')
    ->selectRaw('COUNT(*) AS order_count')
    ->selectRaw('SUM(total) AS revenue')
    ->groupBy('status', 'currency')
    ->get();

This returns one row per (status, currency) pair. Summing money from different currencies into one figure would be a domain error even if SQL accepted it.

Grouping by a MySQL expression

$monthly = DB::table('orders')
    ->selectRaw("DATE_FORMAT(created_at, '%Y-%m') AS month")
    ->selectRaw('SUM(total) AS revenue')
    ->groupByRaw("DATE_FORMAT(created_at, '%Y-%m')")
    ->orderBy('month')
    ->get();

DATE_FORMAT is MySQL-specific. That is a legitimate raw expression, but it creates a portability dependency that should be visible in tests/documentation.

Joined aggregates: check row multiplication

$units = DB::table('orders')
    ->join('order_items', 'orders.id', '=', 'order_items.order_id')
    ->where('orders.status', 'paid')
    ->select('order_items.product_id')
    ->selectRaw('SUM(order_items.quantity) AS units_sold')
    ->groupBy('order_items.product_id')
    ->havingRaw('SUM(order_items.quantity) >= ?', [100])
    ->get();

If you add another one-to-many join, each order-item row can be duplicated before aggregation and inflate counts/sums. Check the intermediate row grain before trusting the aggregate.

Debugging checklist

What does one output row represent?
Which rows are removed by WHERE?
Which expressions form the grouping key?
Does every nonaggregate belong at that grain or follow by functional dependency?
Do joins multiply source rows?
How do nulls affect COUNT(column), SUM(), and AVG()?
Are raw values bound safely?

Related guides

Sources and further reading

Topics: Database Queries & Eloquent