Of course if you want to get complicated and have all the information on one line, I just figured out another option:
SELECT SchoolTest.SchoolNum, Sum(SWITCH( SchoolTest.Status = "Pending", SchoolTest.Cost, SchoolTest.Status="Final", 0)) As [Amount in Progress], Sum(SWITCH( SchoolTest.Status = "Pending", 0, SchoolTest.Status="Final", SchoolTest.Cost)) As [Amount Committed] FROM SchoolTest GROUP BY SchoolNum;
Gives:
SchoolNum Amount in Progress Amount Committed
1 1700 5600
2 0 15000
3 2600 52800
Which are the same results as the last query and much prettier.