1

i have used the query function in SlamData. My code:

SELECT  
DATE_PART("year",thedate) AS year,DATE_PART("month",thedate) AS month,
SUM(runningPnL) AS PnL
FROM "/Mickey/testdb/sampledata3" AS c

GROUP BY DATE_PART("year", thedate) ,DATE_PART("month", thedate) order by DATE_PART("year", thedate) ,DATE_PART("month", thedate)

The extract of my table: 

PnL                 month      year  
-1651.8752           1         2001  
17180.4776           2         2001  
48207.54560000001    3         2001

Now, how can i find the cumulative sum of the PnL?
eg.-1651.8752 for the first month
15528.6024 for the second month
Thank you very much >.<

Community
  • 1
  • 1
Mickey Lau
  • 19
  • 1

2 Answers2

0

I am generating sample data same as you for cumulative sum. Hope from this you get some idea.

Create table tempData 
 (
   pnl float,
   [month] int,
   [year] int
 )
 Go
 insert into tempData values ( -1651.8752, 1,2001)
 insert into tempData values ( 17180.4776, 2,2001)
 insert into tempData values ( 48207.54560000001, 3,2001)

 Select * , (SELECT SUM(Alias.pnl)
         FROM tempData As Alias
         WHERE Alias.[Month] <= tempData.[Month]
         ) As CumulativSUM
 FROm tempData
 ORDER BY tempData.[MOnth]
Snehal
  • 1,070
  • 12
  • 20
  • thank you very much. As i am using SlamData and i am new to it, i have these errors when operating the code: operator ';' expected; identifier table . how can i solve it? thx a lot! – Mickey Lau Jun 29 '16 at 13:54
0

done my code is

SELECT a1.year, a1.month, a1.PnL, a1.PnL/(SUM(a2.PnL)+125000) as Running_Total

FROM/Mickey/testdb/sampledata6as a1,/Mickey/testdb/sampledata6as a2 WHERE (a1.month > a2.month And a1.year=a2.year) or (a1.year>a2.year) GROUP BY a1.year, a1.month,a1.PnL ORDER BY a1.year,a1.month ASC;

Mickey Lau
  • 19
  • 1