SQL Sub Select Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm looking the the SQL that will provide a running total in my data. I want the CumlAmount column to be a running sum of PymtAmount field for each SSN group. The data looks like this:


SSN PymtDate PymtAmount CumlAmount
585042115 1/31/05 $17,000.00 $17,000.00
585042115 1/31/06 $17,000.00 $34,000.00
585042115 1/31/07 $17,000.00 $51,000.00
585042115 1/31/08 $17,000.00 $68,000.00
012345678 1/31/06 $10,000.00 $10,000.00
012345679 1/31/07 $10,000.00 $20,000.00
012345680 1/31/08 $10,000.00 $30,000.00
012345681 1/31/09 $10,000.00 $40,000.00

Could someone provide the SQL to complete this task? Thanks!
 
Try the following for the field value of CumlAmount:

CumlAmount: (SELECT Sum(VT.PymtAmount) FROM YourTable as
VT WHERE VT.SSN = YourTable.SSN AND VT.PymtDate <=
YourTable.PymtDate)

Of course, the main query should also sort ascending on
the SSN and PymtDate to look the way you want.

HTH, post back to let me know if it works.

-Ted Allen
-----Original Message-----
I'm looking the the SQL that will provide a running
Click to expand...
total in my data. I want the CumlAmount column to be a
running sum of PymtAmount field for each SSN group. The
data looks like this:
 
Your advice was right on the money. Seems like I'm doing "Running Sums" quite frequently, but I can never get the syntax exactly right.

Thanks for the help!
 
Hi Kirk,

My pleasure to help.

Yeah, it does take a little while to get used to the
query/subquery syntax, but it sure does open a lot of
possibilities once you get it down. I found it useful
when I was learning them to do the subquery as a stand-
alone query using the builder, and then switch to sql
view and copy the sql. You can even do the sql for
correlated subqueries by inserting the table twice and
aliasing one of them, then linking them appropriately and
setting the appropriate conditions. Then you can just
switch to sql view and copy the sql for use as your
subquery.

-Ted Allen
-----Original Message-----
Your advice was right on the money. Seems like I'm
Click to expand...
doing "Running Sums" quite frequently, but I can never
get the syntax exactly right.
 
Back
Top