0

I`d like to know if someone could help me building a query or php code that given the following table can produce the output on column "desired output".

Basically, the query or php code need to sum the financialvol col by symbol and display the results at each calculation or when openposition is 0.

 tid    | time      | symbol | openposition  | financialvol | desired output
 87646  | 10:29:38  | CTIP3  | 400           | 8744.00      |
 87645  | 10:29:47  | CTIP3  | 800           | 8732.00      |
 87644  | 10:29:52  | CTIP3  | 1000          | 4366.00      |
 87643  | 10:30:02  | CTIP3  | 1200          | 4368.00      |
 87642  | 10:30:06  | CTIP3  | 1400          | 4364.00      |
 87639  | 10:31:57  | CTIP3  | 1200          | -4382.00     |
 87638  | 10:32:04  | CTIP3  | 1000          | -4372.00     |
 87637  | 10:32:08  | CTIP3  | 800           | -4362.00     |
 87636  | 10:32:14  | CTIP3  | 600           | -4388.00     |
 87635  | 10:32:40  | CTIP3  | 0             | -13098.00    | -28
 87647  | 10:28:54  | PCAR4  | 400           | 40480.00     |
 87641  | 10:31:30  | PCAR4  | 200           | -20246.00    |
 87640  | 10:31:41  | PCAR4  | 0             | -20340.00    | -106
 87651  | 10:27:42  | TIMP3  | 1500          | 12345.00     |
 87650  | 10:27:48  | TIMP3  | 11500         | 82300.00     |
 87649  | 10:28:09  | TIMP3  | 1500          | -82600.00    |
 87648  | 10:28:23  | TIMP3  | 0             | -12345.00    | -300
 87657  | 10:26:44  | VIVT4  | -500          | -23710.00    |
 87656  | 10:26:53  | VIVT4  | 0             | 23700.00     | -10
 87655  | 10:26:59  | VIVT4  | -500          | -23740.00    |
 87653  | 10:27:00  | VIVT4  | -1000         | -23740.00    |
 87654  | 10:27:00  | VIVT4  | -1500         | -23740.00    |
 87652  | 10:27:24  | VIVT4  | 0             | 71055.00     | -175

Thanks everyone for looking and trying!

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Thiago
  • 45
  • 6

2 Answers2

0

I believe you're looking for something like this.

For this to make any sense, you'll want to sort your table as: order by time asc

Community
  • 1
  • 1
Mark
  • 4,970
  • 5
  • 42
  • 66
  • not quite as my issue is having a running total per symbol resetting the running total at each time openposition is 0. – Thiago Oct 04 '13 at 20:39
0
SELECT x.*
     , CASE WHEN x.openposition = 0 THEN SUM(y.financialvol) ELSE '' END desired
  FROM my_table x 
  JOIN my_table y 
    ON y.symbol = x.symbol AND y.time <= x.time 
 GROUP 
    BY x.symbol, x.time;
Strawberry
  • 33,750
  • 13
  • 40
  • 57