Geeks on Campus

You likely already know that you can use an Aggregate like SUM to reduce multiple query results rows to a single row.  But did you ever think about using it with a couple of functions to display multiple values in a single row?

Examples

A simple example of a SUM to reduce rows, would be summing the offer amounts of all of a student’s awards to display their total offers.

A more advanced example using SUM with functions would be to display the Summer, Fall, and Spring semester amounts on a single row.  For this example I will be using the STNDT_DISB_AWD record.  For you non-Financial Aid people I will clarify that in this record the award amounts are listed by term, so without any aggregate function the amounts would be shown on separate rows.  The next step will be to create three functions, one for each term:

*note: ‘2115’, ‘2118’, and ‘2122’ are our terms for Summer, Fall, and Spring

Use those functions as fields and select the SUM aggregate for them.

Without the functions and aggregate the data could have looked like this:

EMPLID             TERM    OFFER
0012345678   2115      1000.00
0012345678   2118      2000.00
0012345678   2122      2000.00

With the functions and aggregates the data could look like this:

EMPLID             Summer Offer   Fall Offer   Spring Offer
0012345678    1000.00             2000.00     2000.00

I hope you find this useful.

John