The finance team needs one combined transaction total from two payment processors.
The proc_a and proc_b tables store separate transactions, each with an amount. Calculate the sum of transaction amounts across both processors.
Every transaction must contribute to the total, even when two transactions have the same amount.
Ordering: Return a single row; no sorting is required.
Returntotal
About this problem
You practise combining rows from two sources before aggregating them. proc_a and proc_b hold separate transactions from two payment processors, each with an amount, and finance wants one grand total. The skill is getting every transaction into a single column first, then applying SUM once, so the result reflects all money that moved through both processors. The output is a single row with a total column, the kind of figure that gets reconciled against bank deposits, so even a small shortfall matters.
The most common slip is SELECT amount FROM proc_a UNION SELECT amount FROM proc_b, then summing the result. Because only amount is selected, UNION compares rows by amount alone and throws away any repeat. Two different transactions that happen to be the same value, such as a 100 in each processor, become one row, and the total comes out short by exactly that amount. The number still looks reasonable, which is why this survives review. Another route that goes wrong is joining proc_a to proc_b to put amounts side by side: with no shared key, that join multiplies rows and inflates the sum. Adding two separate totals has its own catch: SUM over an empty table returns NULL, and a number plus NULL is NULL.
Read the lesson: Set Operations (P09) →