如何将主记录及子表关联记录的成本求和至"total Cost"列

Hey there! Let's tackle your two cost summation questions one by one. I'll cover common spreadsheet (like Excel) and database (like SQL) scenarios since you didn't specify the exact tool you're using—these logic patterns apply to most tools with master-subtable structures too.

1. Summing Costs in a Subtable #

If you need to calculate the total cost for each master record's associated subtable entries:

For Spreadsheets (e.g., Excel, Google Sheets) #

Use the SUMIF function to match the master record's ID with the subtable's linked ID, then sum the corresponding cost values.

• Assume:
  • Master table has an ID column (e.g., A:A in MainTable)
  • Subtable has a linked master ID column (e.g., A:A in SubTable) and a cost column (e.g., C:C in SubTable)
• Formula (enter in the master table's sub-total cell):

  =SUMIF(SubTable!$A:$A, A2, SubTable!$C:$C)
  

  A2 refers to the ID of the current master record in your main table. Drag this formula down to apply it to all master records.

For Databases (e.g., SQL) #

Use a GROUP BY clause to aggregate subtable costs by the linked master ID:

SELECT MainID, SUM(Cost) AS SubTotalCost
FROM SubTable
GROUP BY MainID;

This query returns each master ID along with the total cost of all its associated subtable entries.

2. Calculating Total Cost (Master Record + Subtable Entries) #

To combine the master record's own cost with the subtable's total cost and populate the total Cost column:

For Spreadsheets (e.g., Excel, Google Sheets) #

Add the master record's cost to the subtable sum we calculated earlier.

• Assume the master table has its own cost column (e.g., B:B in MainTable)
• Formula (enter in the total Cost column cell):

  =B2 + SUMIF(SubTable!$A:$A, A2, SubTable!$C:$C)
  

  B2 is the cost of the current master record. If a master record has no subtable entries, SUMIF will return 0 automatically, so no extra handling is needed.

For Databases (e.g., SQL) #

Use a LEFT JOIN to combine the master table with the aggregated subtable data, then add the costs together. Use COALESCE to handle cases where a master record has no subtable entries (to avoid NULL values):

SELECT
  m.ID,
  m.MasterCost + COALESCE(s.SubTotalCost, 0) AS "total Cost"
FROM MainTable m
LEFT JOIN (
  SELECT MainID, SUM(Cost) AS SubTotalCost
  FROM SubTable
  GROUP BY MainID
) s ON m.ID = s.MainID;

This query returns every master record with its full total cost, including cases where there are no linked subtable entries.

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