如何对文档中bills数组的total按id去重求和（DISTINCT SUM）

Hey there! Let's tackle this problem where you need to sum the total values but only count each unique id once—even if it appears multiple times in your data. Your example expects 30.0 (20.0 for id "1" + 10.0 for id "2"), and here are a couple of practical ways to get that result depending on your environment:

JavaScript Solution #

If you're handling this data in JavaScript (like frontend code or Node.js), you can first deduplicate entries by id, then sum the unique total values:

Using Map (cleaner for unique key handling) #

const bills = [ { id: "1", total: 20.0 }, { id: "1", total: 20.0 }, { id: "2", total: 10.0 } ];

// Step 1: Keep only one entry per unique id
const uniqueBills = new Map();
bills.forEach(bill => {
  // Skip if the id is already in the map
  if (!uniqueBills.has(bill.id)) {
    uniqueBills.set(bill.id, bill.total);
  }
});

// Step 2: Sum all unique total values
const distinctSum = Array.from(uniqueBills.values()).reduce((acc, currTotal) => acc + currTotal, 0);

console.log(distinctSum); // Outputs 30.0

Using a Plain Object (simpler alternative) #

const bills = [ { id: "1", total: 20.0 }, { id: "1", total: 20.0 }, { id: "2", total: 10.0 } ];

constUniqueEntries = {};
bills.forEach(bill => {
  if (!uniqueEntries[bill.id]) {
    uniqueEntries[bill.id] = bill.total;
  }
});

const distinctSum = Object.values(uniqueEntries).reduce((sum, total) => sum + total, 0);
console.log(distinctSum); // Outputs 30.0

SQL Solution #

If this data lives in a database, you can use a subquery to fetch unique id + total pairs first, then sum those values:

SELECT SUM(total) AS distinct_total_sum
FROM (
    -- Subquery returns only one entry per unique id
    SELECT DISTINCT id, total
    FROM your_bills_table
) AS unique_bills;

This query creates a temporary set of unique entries, then sums the total column from that set—exactly what you need to get the 30.0 result.

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