如何用PHP和MySQL在单循环中计算多表月度总和及解决SQL报错

Let’s break down what’s going wrong here and fix it step by step to get the exact monthly totals you’re expecting.

First, Let’s Identify the Issues #

1. Invalid Column Reference: Your t2 subquery tries to access t1.total_income_amount, but t1 is a separate subquery—t2 can’t see its fields. That’s why you’re getting the 1054 - Unknown column error.
2. Duplicate/Incorrect Columns: In t2, you duplicated the month column definition, and you referenced due_date instead of Table A’s actual remaining_date field.
3. Missing Months: When you removed the invalid column, your query only joined months present in both tables. Months with data in only one table (like February in your sample) got excluded.
4. Date Parsing Mistake: Your dates are in dd/mm/yyyy format, but MySQL defaults to parsing strings as mm/dd/yyyy. This means dates like 4/1/2018 were being treated as April instead of January, throwing off your monthly aggregates.
5. PHP Typo: Your loop uses $row['total_pay'] but your query defines total_pay_remaining—this would cause an undefined index warning once the SQL is fixed.

The Corrected SQL Query #

We’ll first aggregate data from both tables (with correct date parsing), then merge them to include all months (even those with data in only one table). Here’s the working query:

SELECT 
    month,
    SUM(total_advance) AS total_advance,
    SUM(total_pay_remaining) AS total_pay_remaining,
    SUM(total_income_amount) AS total_income_amount
FROM 
    (
        -- Aggregate Table A data with correct dd/mm/yyyy date parsing
        SELECT 
            MONTH(STR_TO_DATE(date, '%d/%m/%Y')) AS month,
            SUM(advanced) AS total_advance,
            SUM(payed_remaining) AS total_pay_remaining,
            0 AS total_income_amount
        FROM A
        GROUP BY MONTH(STR_TO_DATE(date, '%d/%m/%Y'))
        
        UNION ALL
        
        -- Aggregate Table B data with correct dd/mm/yyyy date parsing
        SELECT 
            MONTH(STR_TO_DATE(date, '%d/%m/%Y')) AS month,
            0 AS total_advance,
            0 AS total_pay_remaining,
            SUM(amount) AS total_income_amount
        FROM B
        GROUP BY MONTH(STR_TO_DATE(date, '%d/%m/%Y'))
    ) combined_data
GROUP BY month
ORDER BY month;

This query ensures every month with data is included, fills in 0 for missing values from the other table, and correctly parses your dd/mm/yyyy dates.

Fixed PHP Code #

Update your PHP code to use the corrected query, fix the typo, and calculate totals properly:

$monthly_res = $con->prepare("
    SELECT 
        month,
        SUM(total_advance) AS total_advance,
        SUM(total_pay_remaining) AS total_pay_remaining,
        SUM(total_income_amount) AS total_income_amount
    FROM 
        (
            SELECT MONTH(STR_TO_DATE(date, '%d/%m/%Y')) AS month, SUM(advanced) AS total_advance, SUM(payed_remaining) AS total_pay_remaining, 0 AS total_income_amount FROM A GROUP BY MONTH(STR_TO_DATE(date, '%d/%m/%Y'))
            UNION ALL
            SELECT MONTH(STR_TO_DATE(date, '%d/%m/%Y')) AS month, 0 AS total_advance, 0 AS total_pay_remaining, SUM(amount) AS total_income_amount FROM B GROUP BY MONTH(STR_TO_DATE(date, '%d/%m/%Y'))
        ) combined_data
    GROUP BY month
    ORDER BY month
");
$monthly_res->execute();

// Start <tbody> once outside the loop for cleaner HTML
echo "<tbody>";
while ($row = $monthly_res->fetch(PDO::FETCH_ASSOC)) {
    $month = $row['month'];
    $dt = DateTime::createFromFormat('!m', $month);
    $month_name = $dt->format('F');
    // Calculate total using the correct column names
    $total = $row['total_advance'] + $row['total_pay_remaining'] + $row['total_income_amount'];
    echo "<tr> <td>".$month_name."</td> <td>".$total."/-</td> </tr>";
}
echo "</tbody>";

Verifying Your Expected Results #

With this code, you’ll get exactly the totals you want:

• January: Sum of Table A’s advanced (400+600+800+400=2200) + payed_remaining (800+600+200+300=1900) + Table B’s amount (900+600+300=1800) = 2200+1900+1800=5900
• February: Sum of Table A’s advanced (600+800=1400) + payed_remaining (200+400=600) + Table B’s amount (400+800=1200) =1400+600+1200=3200

内容的提问来源于stack exchange，提问作者Permendra Singh