groupBy(), having(), havingRaw()
Concept
groupBy(), having(), and aggregates are used to compute summary statistics across groups of rows — the SQL equivalent of PHP's array_group_by + reduce.
groupBy(string ...$columns): Groups result rows by the specified columns. Every SELECT column must either be in groupBy or be an aggregate function (COUNT, SUM, etc.).
having(string $column, $operator, $value): Filters on grouped results (like WHERE but for groups). Applied after grouping. Typically used with aggregate conditions.
havingRaw(string $sql, array $bindings = []): Raw SQL HAVING clause.
groupByRaw(string $sql): Raw SQL GROUP BY expression.
Common aggregate functions in SQL (used in selectRaw/DB::raw):
COUNT(*)— count all rows in group.COUNT(DISTINCT column)— count unique values.SUM(column)— total.AVG(column)— average.MAX(column)/MIN(column)— extremes.GROUP_CONCAT(column SEPARATOR ',')— MySQL: concatenate values.JSON_ARRAYAGG(column)— MySQL 5.7+: JSON array of values.
ROLLUP (MySQL): ->groupByRaw('category WITH ROLLUP') — adds subtotal rows.
Important MySQL "only_full_group_by" mode: By default, MySQL requires all non-aggregate SELECT columns to be in GROUP BY. Laravel's MySQL config disables this by default, but production servers may have it enabled. Write standard SQL to be safe.
Code Example
<?php
use Illuminate\Support\Facades\DB;
// Basic groupBy + aggregate
$salesByCategory = DB::table('order_items')
->join('products', 'products.id', '=', 'order_items.product_id')
->select('products.category')
->selectRaw('COUNT(*) as item_count, SUM(order_items.quantity * order_items.price) as revenue')
->groupBy('products.category')
->orderByDesc('revenue')
->get();
// having — filter groups
$topCustomers = DB::table('orders')
->select('user_id')
->selectRaw('COUNT(*) as order_count, SUM(total) as lifetime_value')
->groupBy('user_id')
->having('lifetime_value', '>=', 1000) // only high-value customers
->orderByDesc('lifetime_value')
->get();
// havingRaw
$activeCategories = DB::table('products')
->select('category')
->selectRaw('COUNT(*) as product_count')
->groupBy('category')
->havingRaw('COUNT(*) >= ?', [5]) // categories with at least 5 products
->get();
// GROUP BY date function
$dailySales = DB::table('orders')
->selectRaw('DATE(created_at) as sale_date, COUNT(*) as orders, SUM(total) as revenue')
->where('status', 'completed')
->whereBetween('created_at', [now()->startOfMonth(), now()->endOfMonth()])
->groupByRaw('DATE(created_at)')
->orderBy('sale_date')
->get();
// Distinct count
$uniqueBuyers = DB::table('orders')
->selectRaw('COUNT(DISTINCT user_id) as unique_buyers')
->where('status', 'completed')
->value('unique_buyers');
// GROUP_CONCAT — list of values per group (MySQL)
$tagsPerPost = DB::table('posts')
->join('post_tag', 'posts.id', '=', 'post_tag.post_id')
->join('tags', 'tags.id', '=', 'post_tag.tag_id')
->select('posts.id', 'posts.title')
->selectRaw("GROUP_CONCAT(tags.name ORDER BY tags.name SEPARATOR ', ') as tag_list")
->groupBy('posts.id', 'posts.title')
->get();