MySQL单查询实现suspended_bills表账单与报价的双条件列求和

Hey there! Let's sort out this MySQL query issue for you. I’m guessing your original attempt might have messed up the conditional logic for separating bill and quotation totals, or maybe tried to sum amounts without properly filtering each type. Here are two reliable ways to get both totals in a single query:

Approach 1: Get Totals in Side-by-Side Columns #

This method returns both sums in one row, which is perfect if you need to display them together:

SELECT
    SUM(CASE WHEN type = 'bill' THEN amount ELSE 0 END) AS total_bill_amount,
    SUM(CASE WHEN type = 'quotation' THEN amount ELSE 0 END) AS total_quotation_amount
FROM suspended_bills;

A quick note: I assumed your table uses a type field to distinguish bills from quotations. If your actual field name is different (like document_type), just swap that in. The CASE WHEN clause checks each record’s type—only adding the amount to the corresponding total if it matches, otherwise contributing 0.

Approach 2: Group Totals by Document Type #

If you’d rather see each total in its own row (great for reporting or iterating over results), use GROUP BY:

SELECT
    type,
    SUM(amount) AS total_amount
FROM suspended_bills
WHERE type IN ('bill', 'quotation') -- Optional: add this if your table has other document types
GROUP BY type;

This will output two rows: one for bill with its total, and one for quotation with its total. The WHERE clause is optional but useful if your table includes other types you don’t want to include in the sums.

Common mistakes to watch out for:

• Forgetting to filter or conditionally sum, leading to a single total of all amounts
• Using incorrect field names (double-check that type and amount match your actual schema)
• Accidentally using OR instead of separate conditional checks, which can cause overcounting

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