Query Challenge – Combine a column from multiple rows per item into one row per item showing each column and the sum of the columns.
-
After all my experimentation, it’s time to draw on more experience than my own. Given the table structure (and sample data) below, with multiple rows per bank, what query/queries would I need to produce the result of one row per bank with each Fee from the two quarters and the sum of the Fees? If a bank is new or closed in the selected quarters then there will only be a record for the bank in the quarter when it existed, and for quarters when the bank did not exist, a NULL (or zero) should be shown.
Example: BankFeeTable
BankNo, Quarter, Fee
“101”, “2004Q1”, “1000”
“101”, “2004Q2”, “1100”
“102”, “2004Q1”, “1200”
“103”, “2004Q2”, “1300”
“104”, “2004Q1”, “1400”
“104”, “2004Q2”, “1500”
Desired Result:
BankNo, Qtr1_Fee, Qtr2_Fee, FeeTotal
“101”, “1000”, “1100”, “2100”
“102”, “1200”, “NULL”, “1200”
“103”, “NULL”, “1300”, “1300”
“104”, “1400”, “1500”, “2900”
Thanks
Norm Johnson
"Keep smiling ... it gives your face something happy to do
... and it makes people wonder what you're up to!"
-
Select BankNo,
Sum( Fee * Case Quarter When '2004Q1' then 1 Else 0 End ) As Qtr1_Fee,
Sum( Fee * Case Quarter When '2004Q2' then 1 Else 0 End ) As Qtr2_Fee,
Sum( Fee ) As FeeTotal
From BankFeeTable
Group By BankNo
-
PW, well done! Your solution is a LOT SIMPLER than the gyrations I was going through.
Thanks
Norm Johnson
"Keep smiling ... it gives your face something happy to do
... and it makes people wonder what you're up to!"
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply