Laravel 5.6关联两表Sum+Group By查询报错求助

Hey there! Let's break down why you're hitting this SQLSTATE[42000]: 1055 error and how to fix it.

Why the Error Happens #

MySQL (version 5.7 and above) has ONLY_FULL_GROUP_BY enabled by default. This rule ensures that every column in your SELECT clause is either included in the GROUP BY clause or wrapped in an aggregate function (like SUM, MAX, etc.).

In your query, you're selecting donations.* (which includes unique-per-donation columns like donation_id) and members.*, but only grouping by member_id. MySQL can't determine which specific non-grouped column value to return for each member group—hence the error. Also, naming your summed amount amount clashes with the original donations.amount column, which is confusing.

Solution 1: Get Member-Level Total Donations #

If your goal is to get each member's total donation amount along with their name, adjust your query to only select the member fields you need plus the aggregated total. Since members.id is the primary key, grouping by it (and optionally members.name for broader compatibility) will work:

SELECT 
    members.id,
    members.name,
    SUM(donations.amount) AS total_amount
FROM donations
INNER JOIN members ON donations.member_id = members.id
GROUP BY members.id, members.name

(Note: In MySQL 5.7+, grouping by just members.id is sufficient because the primary key uniquely identifies all other member columns, but adding members.name makes the query compatible with stricter SQL modes.)

Solution 2: Get Individual Donations + Member Total #

If you want to see every single donation record alongside the total amount that member has given, use a window function instead of GROUP BY. This avoids grouping rows together while still calculating the aggregate:

SELECT 
    members.name,
    donations.donation_date,
    donations.amount,
    SUM(donations.amount) OVER (PARTITION BY donations.member_id) AS member_total
FROM donations
INNER JOIN members ON donations.member_id = members.id

What to Avoid #

While you could disable ONLY_FULL_GROUP_BY in your MySQL configuration, this is not recommended. Disabling it would let MySQL return arbitrary values for non-grouped columns (like a random donation_id per member), which leads to inconsistent and unpredictable results.

内容的提问来源于stack exchange，提问作者Shahid Hussain