SQL中生成列`tix_spend`计算异常及总花费求和问题求助

Hey there! Let's tackle those weird tix_spend values first, then add the total spend calculation you need.

The Root Cause of Abnormal tix_spend Values #

Your current query joins Bidder directly with Tickets and Bids without aggregating first—and that's causing a cartesian product problem. Here's what's happening: if a bidder has multiple bid records, every row from their ticket data gets duplicated once per bid. So when you run SUM(t.Quantity*3), you're not just calculating the cost of their tickets—you're multiplying that cost by the number of bids they made.

For example, Alice Carson has 1 ticket but 10 bids: 1*3*10 = 30, which matches your odd result. Bobby Walton has 1 ticket and 11 bids: 1*3*11 = 33, explaining that anomaly too.

The Fixed Query #

To fix this, we need to calculate ticket spend and bid spend separately for each bidder first, then combine the results. This avoids the duplication issue entirely:

SELECT 
    bi.BidderID,
    bi.FirstName || ' ' || bi.LastName AS bidder_name,
    COALESCE(t.total_tix, 0) AS no_of_tix,
    COALESCE(t.tix_spend, 0) AS tix_spend,
    COALESCE(b.bid_spend, 0) AS bid_spend,
    COALESCE(t.tix_spend, 0) + COALESCE(b.bid_spend, 0) AS total_spend
FROM Bidder bi
LEFT JOIN (
    -- Calculate total tickets and ticket cost per bidder
    SELECT 
        BidderID,
        SUM(Quantity) AS total_tix,
        SUM(Quantity * 3) AS tix_spend
    FROM Tickets
    GROUP BY BidderID
) t ON t.BidderID = bi.BidderID
LEFT JOIN (
    -- Calculate total bid amount per bidder
    SELECT 
        BidderID,
        SUM(BidAmount) AS bid_spend
    FROM Bids
    GROUP BY BidderID
) b ON b.BidderID = bi.BidderID
ORDER BY bidder_name
LIMIT 5;

Key Changes Explained: #

• Independent Aggregation: Two subqueries pre-calculate ticket totals and bid totals separately. This ensures each bidder's ticket data is counted only once, no matter how many bids they have.
• LEFT JOIN: Using LEFT JOIN instead of regular JOIN ensures we don't exclude bidders who only have tickets or only have bids—their missing values will show as 0 instead of being filtered out.
• COALESCE: This function replaces NULL values (for bidders with no tickets or no bids) with 0, so our total spend calculation doesn't break.
• Total Spend: We simply add tix_spend and bid_spend together to get the combined total cost for each bidder.

What This Fixes #

• Alice Carson's tix_spend will now correctly show 3 (1 ticket × £3) instead of 30.
• Bobby Walton's tix_spend will show 3 instead of 33.
• You'll get an accurate total_spend column that combines both ticket and bid costs for every bidder.

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