Home » SQL & PL/SQL » SQL & PL/SQL » Sum of last 3 transactions
Sum of last 3 transactions [message #210352] Wed, 20 December 2006 08:21 Go to next message
spohlso
Messages: 19
Registered: April 2006
Junior Member
Hi all,
I tried a search, but couldn't find any threads about this. At any rate, I'm pulling a lot of info out of several joined tables. I've got Username, AccountNumber, TransactionDate, TransactionAmount and several others.

What I need is the sum of the last 3 transactions for each account. I'm not even sure where to start. I'm assuming I need to select TOP 3 or something but how do I let SQL know I want the TOP 3 dates? Anyone know the right way to approach this?
Re: Sum of last 3 transactions [message #210356 is a reply to message #210352] Wed, 20 December 2006 08:34 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
This might help you. Analytical Functions
By
Vamsi
Re: Sum of last 3 transactions [message #210362 is a reply to message #210356] Wed, 20 December 2006 08:38 Go to previous messageGo to next message
spohlso
Messages: 19
Registered: April 2006
Junior Member
Thanks. I'll check that site out.
Re: Sum of last 3 transactions [message #210366 is a reply to message #210352] Wed, 20 December 2006 08:45 Go to previous messageGo to next message
spohlso
Messages: 19
Registered: April 2006
Junior Member
That stuff is a little over my head. Know any sites for slower people? Embarassed
Re: Sum of last 3 transactions [message #210367 is a reply to message #210366] Wed, 20 December 2006 08:51 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Please post your scripts and sample data.

By
Vamsi
Re: Sum of last 3 transactions [message #210371 is a reply to message #210367] Wed, 20 December 2006 09:02 Go to previous messageGo to next message
spohlso
Messages: 19
Registered: April 2006
Junior Member
So far all I have is

SELECT Username, AccountNumber, TransactionDate, TransactionAmount`
FROM Database
ORDER BY AccountNumber, Username, TransactionDate
(I haven't tried to figure out the SUM and GROUP BY stuff yet)

and I get:

Joe 1138 09/11/06 10.01
Joe 1138 08/02/06 113.98
Joe 1138 08/01/06 2.37
Joe 1138 05/05/06 1113.23
Pat 1400 10/10/06 34.57
Pat 1400 10/08/06 88.99
Pat 1400 09/11/06 1.78
Pat 1400 09/01/06 22.33
Pat 1400 08/29/06 111.23
and so on like that.

What I need would be the sum of the last 3 transactions like so:
Joe 1138 126.36
Pat 1400 125.34
and so on for each account in the database.

I'm not even sure where to start. There are over 50 accounts, but everything I've tried to do with TOP 3 has given me 3 records. I need the top 3 dates for each of the 50 accounts.
Re: Sum of last 3 transactions [message #210380 is a reply to message #210371] Wed, 20 December 2006 09:24 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I could think of this. Might not perform that well.
select Username,AccountNumber,sum(TransactionAmount)
  from (select Username,AccountNumber,TransactionAmount
              ,rank() over (partition by AccountNumber
                            order by TransactionDate desc) x
          from Database
)
where x<=3
group by Username,AccountNumber;
By
Vamsi
Re: Sum of last 3 transactions [message #210382 is a reply to message #210380] Wed, 20 December 2006 09:25 Go to previous messageGo to next message
spohlso
Messages: 19
Registered: April 2006
Junior Member
Thanks. I'll give it a shot!
Re: Sum of last 3 transactions [message #210384 is a reply to message #210382] Wed, 20 December 2006 09:31 Go to previous messageGo to next message
spohlso
Messages: 19
Registered: April 2006
Junior Member
Perhaps I should have specified that I'm using SQL 2000? I just tried the query and I get the message:

Server: Msg 195, Level 15, State 10, Line 3
'rank' is not a recognized function name.
Re: Sum of last 3 transactions [message #210386 is a reply to message #210384] Wed, 20 December 2006 09:37 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I have no idea whether row_number() works on SQL 2000 or not.

By
Vamsi
Re: Sum of last 3 transactions [message #210388 is a reply to message #210386] Wed, 20 December 2006 09:45 Go to previous messageGo to next message
spohlso
Messages: 19
Registered: April 2006
Junior Member
Unfortunately no. I just ttried it and got the same result.
Re: Sum of last 3 transactions [message #210389 is a reply to message #210352] Wed, 20 December 2006 09:46 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Perhaps I should have specified that I'm using SQL 2000?
Rhetorical question - Why are you using SQL2000 & posting question to an ORACLE forum?
Re: Sum of last 3 transactions [message #210390 is a reply to message #210386] Wed, 20 December 2006 09:46 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
You might want to try an SQL Server Forum then, not an Oracle FAQ forum
Re: Sum of last 3 transactions [message #210391 is a reply to message #210352] Wed, 20 December 2006 09:50 Go to previous messageGo to next message
spohlso
Messages: 19
Registered: April 2006
Junior Member
YOUCH! I thought this WAS a SQL forum!

Talk about embarrassing!
Re: Sum of last 3 transactions [message #210720 is a reply to message #210391] Fri, 22 December 2006 00:59 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
spohlso wrote on Wed, 20 December 2006 16:50
YOUCH! I thought this WAS a SQL forum!
It IS: SQL is Structured Query Language, not an abbreviation for SQL Server. This is what you get when you don't get your names right...

anyway: good luck!

MHE
Previous Topic: about substitution parameter
Next Topic: Stored Procedure: Fetch Excel data through oracle
Goto Forum:
  


Current Time: Fri Dec 02 16:22:51 CST 2016

Total time taken to generate the page: 0.32259 seconds