Find Aggregated Value within Column..

soum_guli · March 19, 2018, 3:31pm

The Data looks like this

DateTime Price BidOrAsk Quantity Orders Ltp --------------------------------------------------------------2018.03.19T15:30:03.626 273.15 0 23 2 273.05 2018.03.19T15:30:03.626 273.1 0 70 1 273.05 2018.03.19T15:30:03.626 273.05 0 182 1 273.05 2018.03.19T15:30:03.626 273 0 2572 10 273.05 2018.03.19T15:30:03.626 272.95 0 5886 13 273.05 2018.03.19T15:30:03.626 273.2 1 16 1 273.05 2018.03.19T15:30:03.626 273.3 1 444 3 273.05 2018.03.19T15:30:03.626 273.35 1 4959 5 273.05 2018.03.19T15:30:03.626 273.4 1 7862 8 273.05 2018.03.19T15:30:03.626 273.45 1 5323 13 273.05



In the End The data would look like this





    DateTime Price BidOrAsk Quantity Orders Ltp TotalBid_SumOfOrderMultiplyQuantity TotalAsk_SumOfOrderMultiplyQuantity
    --------------------------------------------------------------2018.03.19T15:30:03.626 273.15 0 23 2 273.05 (23*2+70*1+182*1+2572*10+5886*13) . (16*1+444*3+4959*5+7862*8+5323*13)



    




    




    Here is what I have tried



    




    1.Update the table with Order*Multiply



    




    
    `update oqm:(Orders*Quantity) from Tablename`
    <font color='"#000000"'><span style='"font-size:'><br>2. Now I want a sql like this</span></font>



    <font color='"#000000"'><span style='"font-size:'><br></span></font>



    
    `if(DateTime==previousDateTime) and(BidOrAsk=previous BidOrAsk) then sum=sum+oqm`
    




    How do I create this sql in q?



    Please Help

soum_guli · March 19, 2018, 3:39pm

The end output looks like this...



DateTime Ltp TotalBid_<wbr>SumOfOrderMultiplyQuantity TotalAsk_<wbr>SumOfOrderMultiplyQuantity<br>------------------------------<wbr>------------------------------<wbr>--2018.03.19T15:30:03.626 273.05 (23*2+70*1+182*1+2572*10+5886*<wbr>13) . (16*1+444*3+4959*5+7862*8+<wbr>5323*13)</wbr></wbr></wbr></wbr></wbr></wbr>

kpatel9302 · March 19, 2018, 4:02pm

Hi Gully G,

you can try using a by in a select query to group by DateTime and BidorAsk like this:

select totaloqm:sum[oqm] by DateTime,BidOrAsk from (update oqm:(Orders*Quantity) from tab)

DateTime BidOrAsk totaloqm--------------------------------| --------2018.03.19T15:51:15.128 0 | 1025362018.03.19T15:51:15.128 1 | 158238

Regards

Karan Patel

User7 · March 19, 2018, 4:31pm

Hi, Gully G!

I think this bit of code gives you the exact result you are looking for:

((),ind)_1#(ind xcols update ind:i from tablename) lj 1!([]ind:enlist 0;TBSO:exec sum Quant*Orders from tablename where BidAsk=1) lj 1!([] ind:enlist 0;TASO:exec sum Quant*Orders from tablename where BidAsk=0

Please note that the names of the columns may differ slightly.

Cheers!

Alex

soum_guli · March 19, 2018, 5:12pm

Hi Alex,

First of all thanks a lot for the solution. I modified your query (substituting the valid column names)

((),ind)_1#(ind xcols update ind:i from table) lj 1!([]ind:enlist 0;TBSO:exec sum Quantity*Orders from table where BidOrAsk=1) lj 1!([] ind:enlist 0;TASO:exec sum Quantity*Orders from table where BidOrAsk=0)

However this gives only one row as output and the TBSO and TASO gives the total of all the values.

WsDateTimeBidOrAskQuantityOrdersLtpTBSOTASO

2018.03.19 19:34:47.3980501264.159626735896958851

The logic is kinda like

if ((WsDateTime==previous WsDateTime) and(BidOrAsk==prevous BidOrAsk)){if(BidOrAsk ==1)TASO=TASO+latest(Order*Quantity);if(BidOrAsk ==0)TBSO= TBSO+latest(Order*Quantity)}if(WsDate!=previous WsDateTime){TASO=0;TBSO=0;TBS0=latest(Order*Quantity)TASO=latest(Order*Quantity)}

After which the output will contain multiple rows.

User7 · March 19, 2018, 7:19pm

we start with this table:

tab:(DateTime:10#.z.z;Price:(273.15,273.1,273.05,273,272.95,273.2,273.3,273.35,273.4,273.45);BidOrAsk:(5#0),(5#1);Quantity:(23,70,182,2572,5886,16,444,4959,7862,5323);Orders:(2,1,1,10,13,1,3,5,8,13);Ltp:(10#273.05))

DateTime Price BidOrAsk Quantity Orders Ltp

2018.03.19T18:37:24.038 273.15 0        23       2      273.05
2018.03.19T18:37:24.038 273.1 0        70       1      273.05
2018.03.19T18:37:24.038 273.05 0        182      1      273.05
2018.03.19T18:37:24.038 273    0        2572     10     273.05
2018.03.19T18:37:24.038 272.95 0        5886     13     273.05
2018.03.19T18:37:24.038 273.2 1        16       1      273.05
2018.03.19T18:37:24.038 273.3 1        444      3      273.05
2018.03.19T18:37:24.038 273.35 1        4959     5      273.05
2018.03.19T18:37:24.038 273.4 1        7862     8      273.05
2018.03.19T18:37:24.038 273.45 1        5323     13     273.05

Split by bid or ask:
a:select from tab where BidOrAsk=1
b:select from tab where BidOrAsk=0

add a column with the multiplication of Orders and Quantity
update QO:Quantity*Orders from a update QO:Quantity\*Orders from b

Do a moving sum for identical dates
update QO:(last sums QO) by DateTime from a update QO:(last sums QO) by DateTime from b

get last value and join together
flip (first a),'first b

and the final result looks like this:
DateTime Price BidOrAsk Quantity Orders Ltp QO

2018.03.19T19:09:34.041 273.2 1 16 1 273.05 158238
2018.03.19T19:09:34.041 273.15 0 23 2 273.05 102536

I broke this into as many bits as I could to see if I got the logic right. Please let me know if this is what you are looking for and I will try to squash everything together into a one liner!

Cheers!

Alex

Flying1 · March 20, 2018, 3:17am

Since the problem description is not really clear, I’m trying to guess what you want here.

Assuming Data is the original table here:

q)Data

DateTime Price BidOrAsk Quantity Orders Ltp

--------------------------------------------------------------

2018.03.20T03:02:23.346 273.15 0 23 2 273.05

2018.03.20T03:02:23.346 273.1 0 70 1 273.05

2018.03.20T03:02:23.346 273.05 0 182 1 273.05

2018.03.20T03:02:23.346 273 0 2572 10 273.05

2018.03.20T03:02:23.346 272.95 0 5886 13 273.05

2018.03.20T03:02:23.346 273.2 1 16 1 273.05

2018.03.20T03:02:23.346 273.3 1 444 3 273.05

2018.03.20T03:02:23.346 273.35 1 4959 5 273.05

2018.03.20T03:02:23.346 273.4 1 7862 8 273.05

2018.03.20T03:02:23.346 273.45 1 5323 13 273.05

q)/ This is to get the incremental sums

q)update TotalBid:sums QuantityOrdersBidOrAsk=0,TotalAsk:sums QuantityOrdersBidOrAsk=1 by DateTime.date from Data

DateTime Price BidOrAsk Quantity Orders Ltp TotalBid TotalAsk

--------------------------------------------------------------------------------

2018.03.20T03:02:23.346 273.15 0 23 2 273.05 46 0

2018.03.20T03:02:23.346 273.1 0 70 1 273.05 116 0

2018.03.20T03:02:23.346 273.05 0 182 1 273.05 298 0

2018.03.20T03:02:23.346 273 0 2572 10 273.05 26018 0

2018.03.20T03:02:23.346 272.95 0 5886 13 273.05 102536 0

2018.03.20T03:02:23.346 273.2 1 16 1 273.05 102536 16

2018.03.20T03:02:23.346 273.3 1 444 3 273.05 102536 1348

2018.03.20T03:02:23.346 273.35 1 4959 5 273.05 102536 26143

2018.03.20T03:02:23.346 273.4 1 7862 8 273.05 102536 89039

2018.03.20T03:02:23.346 273.45 1 5323 13 273.05 102536 158238

q)/ This is to get the final sum only

q)select TotalBid:sum QuantityOrdersBidOrAsk=0,TotalAsk:sum QuantityOrdersBidOrAsk=1 by DateTime.date from Data

date | TotalBid TotalAsk

----------| -----------------

2018.03.20| 102536 158238

soum_guli · March 20, 2018, 11:35am

@Alex yes that is the correct logic..how would it look in single statement? Also for other complex queries like these where can I read them? Documentation really sucks!

Flying1 · March 21, 2018, 2:14am

Similar to the queries I wrote earlier on:

q)Data

DateTime Price BidOrAsk Quantity Orders Ltp

--------------------------------------------------------------

2018.03.21T02:06:34.203 273.15 0 23 2 273.05

2018.03.21T02:06:34.203 273.1 0 70 1 273.05

2018.03.21T02:06:34.203 273.05 0 182 1 273.05

2018.03.21T02:06:34.203 273 0 2572 10 273.05

2018.03.21T02:06:34.203 272.95 0 5886 13 273.05

2018.03.21T02:06:34.203 273.2 1 16 1 273.05

2018.03.21T02:06:34.203 273.3 1 444 3 273.05

2018.03.21T02:06:34.203 273.35 1 4959 5 273.05

2018.03.21T02:06:34.203 273.4 1 7862 8 273.05

2018.03.21T02:06:34.203 273.45 1 5323 13 273.05

q)delete Date from select first DateTime,first Price,first Quantity,first Orders,first Ltp,QO:sum Quantity*Orders by Date:DateTime.date,BidOrAsk from Data

BidOrAsk DateTime Price Quantity Orders Ltp QO

---------------------------------------------------------------------

0 2018.03.21T02:06:34.203 273.15 23 2 273.05 102536

1 2018.03.21T02:06:34.203 273.2 16 1 273.05 158238

soum_guli · March 21, 2018, 9:38am

@Flying the query which you proposed gives only 2 output for multiple rows.
Let take the example once more. If below is the table of the data

DateTime Price BidOrAsk Quantity Orders Ltp

------------------------------------------------------------–

2018.03.21T02:06:34.203 273.15 0 10 1 273.05

2018.03.21T02:06:34.203 273.1 0 10 1 273.05

2018.03.21T02:06:34.203 273.05 0 10 1 273.05

2018.03.21T02:06:34.203 273 0 10 1 273.05

2018.03.21T02:06:34.203 272.95 0 10 1 273.05

2018.03.21T02:06:34.203 273.2 1 10 2 273.05

2018.03.21T02:06:34.203 273.3 1 10 2 273.05

2018.03.21T02:06:34.203 273.35 1 10 2 273.05

2018.03.21T02:06:34.203 273.4 1 10 2 273.05

2018.03.21T02:06:34.203 273.45 1 10 2 273.05

2018.03.21T02:06:35.203 273.15 0 10 4 273.05

2018.03.21T02:06:35.203 273.1 0 10 4 273.05

2018.03.21T02:06:35.203 273.05 0 10 4 273.05

2018.03.21T02:06:35.203 273 0 10 4 273.05

2018.03.21T02:06:35.203 272.95 0 10 4 273.05

2018.03.21T02:06:35.203 273.2 1 10 5 273.05

2018.03.21T02:06:35.203 273.3 1 10 5 273.05

2018.03.21T02:06:35.203 273.35 1 10 5 273.05

2018.03.21T02:06:35.203 273.4 1 10 5 273.05

2018.03.21T02:06:35.203 273.45 1 10 5 273.05

The output looks like this

DateTime Price BidOrAsk Ltp Total

------------------------------------------------------------–

2018.03.21T02:06:34.203 273.15 0 273.05 (10*1)+(10*1)+(10*1)+(10*1)+(10*1)=50

2018.03.21T02:06:34.203 273.1 1 273.05 (10*2)+(10*2)+(10*2)+(10*2)+(10*2)=100

2018.03.21T02:06:35.203 273.15 0 273.05 (10*4)+(10*4)+(10*4)+(10*4)+(10*4)=200

2018.03.21T02:06:35.203 273.1 1 273.05 . (10*5)+(10*5)+(10*5)+(10*5)+(10*5)=250

Thanks,

Gully

User7 · March 21, 2018, 10:00am

Hi!

I uploaded the one liner some time ago but it seems that it was blocked for some reason. Here it is:

(first each {select from tab where BidOrAsk=x}'[0 1]),'raze {select last QO from (update QO:sums Quantity*Orders by DateTime from tab where BidOrAsk=x) where not null QO}'[0 1]

Cheers!

Alex

soum_guli · March 21, 2018, 11:17am

Hi @alex,
The query gives only one output for multiple time-frames. However I want two outputs for each DateTime

I have updated the example it with the better example. The confusion I think was the previous example was only catering to one timeframe…If below is the table of the data

DateTime Price BidOrAsk Quantity Orders Ltp

------------------------------------------------------------–

2018.03.21T02:06:34.203 273.15 0 10 1 273.05

2018.03.21T02:06:34.203 273.1 0 10 1 273.05

2018.03.21T02:06:34.203 273.05 0 10 1 273.05

2018.03.21T02:06:34.203 273 0 10 1 273.05

2018.03.21T02:06:34.203 272.95 0 10 1 273.05

2018.03.21T02:06:34.203 273.2 1 10 2 273.05

2018.03.21T02:06:34.203 273.3 1 10 2 273.05

2018.03.21T02:06:34.203 273.35 1 10 2 273.05

2018.03.21T02:06:34.203 273.4 1 10 2 273.05

2018.03.21T02:06:34.203 273.45 1 10 2 273.05

2018.03.21T02:06:35.203 273.15 0 10 4 273.05

2018.03.21T02:06:35.203 273.1 0 10 4 273.05

2018.03.21T02:06:35.203 273.05 0 10 4 273.05

2018.03.21T02:06:35.203 273 0 10 4 273.05

2018.03.21T02:06:35.203 272.95 0 10 4 273.05

2018.03.21T02:06:35.203 273.2 1 10 5 273.05

2018.03.21T02:06:35.203 273.3 1 10 5 273.05

2018.03.21T02:06:35.203 273.35 1 10 5 273.05

2018.03.21T02:06:35.203 273.4 1 10 5 273.05

2018.03.21T02:06:35.203 273.45 1 10 5 273.05

The output looks like this

DateTime Price BidOrAsk Ltp Total

------------------------------------------------------------–

2018.03.21T02:06:34.203 273.15 0 273.05 (101)+(10*1)+(10*1)+(10*1)+(101)=50

2018.03.21T02:06:34.203 273.1 1 273.05 (102)+(10*2)+(10*2)+(10*2)+(102)=100

2018.03.21T02:06:35.203 273.15 0 273.05 (104)+(10*4)+(10*4)+(10*4)+(104)=200

2018.03.21T02:06:35.203 273.1 1 273.05 . (10*5)+(10*5)+(10*5)+(10*5)+(10*5)=250

soum_guli · March 21, 2018, 11:27am

I dont know why the my previous comment not showing the example.

@Alex please have a look at this example. I think eveyone is having a confusion as the previous example was showing only one datatime.

DateTime Price BidOrAsk Quantity Orders Ltp

------------------------------------------------------------–

2018.03.21T02:06:34.203 273.15 0 10 1 273.05

2018.03.21T02:06:34.203 273.1 0 10 1 273.05

2018.03.21T02:06:34.203 273.05 0 10 1 273.05

2018.03.21T02:06:34.203 273 0 10 1 273.05

2018.03.21T02:06:34.203 272.95 0 10 1 273.05

2018.03.21T02:06:34.203 273.2 1 10 2 273.05

2018.03.21T02:06:34.203 273.3 1 10 2 273.05

2018.03.21T02:06:34.203 273.35 1 10 2 273.05

2018.03.21T02:06:34.203 273.4 1 10 2 273.05

2018.03.21T02:06:34.203 273.45 1 10 2 273.05

2018.03.21T02:06:35.203 273.15 0 10 4 273.05

2018.03.21T02:06:35.203 273.1 0 10 4 273.05

2018.03.21T02:06:35.203 273.05 0 10 4 273.05

2018.03.21T02:06:35.203 273 0 10 4 273.05

2018.03.21T02:06:35.203 272.95 0 10 4 273.05

2018.03.21T02:06:35.203 273.2 1 10 5 273.05

2018.03.21T02:06:35.203 273.3 1 10 5 273.05

2018.03.21T02:06:35.203 273.35 1 10 5 273.05

2018.03.21T02:06:35.203 273.4 1 10 5 273.05

2018.03.21T02:06:35.203 273.45 1 10 5 273.05

The output looks like this

DateTime Price BidOrAsk Ltp Total

------------------------------------------------------------–

2018.03.21T02:06:34.203 273.15 0 273.05 (101)+(10*1)+(10*1)+(10*1)+(101)=50

2018.03.21T02:06:34.203 273.1 1 273.05 (102)+(10*2)+(10*2)+(10*2)+(102)=100

2018.03.21T02:06:35.203 273.15 0 273.05 (104)+(10*4)+(10*4)+(10*4)+(104)=200

2018.03.21T02:06:35.203 273.1 1 273.05 . (10*5)+(10*5)+(10*5)+(10*5)+(10*5)=250

Thanks,

Gully

jemma_borland · March 21, 2018, 2:02pm

Hi Gully

I believe this line should do what you want, as originally suggested by Karan:

0!select totaloqm:sum oqm by DateTime,BidOrAsk from update oqm:Orders*Quantity from tab

If you wish to have the price and Ltp in your output, then you can add first price, first Ltp into the select statement like so:

0!select first price,first Ltp,totaloqm:sum oqm by DateTime,BidOrAsk from update oqm:Orders*Quantity from tab

Sample table:

q)tab

DateTime Price BidOrAsk Quantity Orders Ltp

--------------------------------------------------------------

2018.03.21T11:25:01.307 273.15 0 10 1 273.05

2018.03.21T11:25:01.307 273.1 0 10 1 273.05

2018.03.21T11:25:01.307 273.05 0 10 1 273.05

2018.03.21T11:25:01.307 273 0 10 1 273.05

2018.03.21T11:25:01.307 272.95 0 10 1 273.05

2018.03.21T11:25:01.307 273.2 1 10 2 273.05

2018.03.21T11:25:01.307 273.3 1 10 2 273.05

2018.03.21T11:25:01.307 273.35 1 10 2 273.05

2018.03.21T11:25:01.307 273.4 1 10 2 273.05

2018.03.21T11:25:01.307 273.45 1 10 2 273.05

2018.03.21T12:25:01.307 273.15 0 10 4 273.05

2018.03.21T12:25:01.307 273.1 0 10 4 273.05

2018.03.21T12:25:01.307 273.05 0 10 4 273.05

2018.03.21T12:25:01.307 273 0 10 4 273.05

2018.03.21T12:25:01.307 272.95 0 10 4 273.05

2018.03.21T12:25:01.307 273.2 1 10 5 273.05

2018.03.21T12:25:01.307 273.3 1 10 5 273.05

2018.03.21T12:25:01.307 273.35 1 10 5 273.05

2018.03.21T12:25:01.307 273.4 1 10 5 273.05

2018.03.21T12:25:01.307 273.45 1 10 5 273.05

Sample output:

q)0!select first Price,first Ltp,totaloqm:sum oqm by DateTime,BidOrAsk from update oqm:Orders*Quantity from tab

DateTime BidOrAsk Price Ltp totaloqm

-------------------------------------------------------

2018.03.21T11:25:01.307 0 273.15 273.05 50

2018.03.21T11:25:01.307 1 273.2 273.05 100

2018.03.21T12:25:01.307 0 273.15 273.05 200

2018.03.21T12:25:01.307 1 273.2 273.05 250

Please let me know if this helps.

Best regards,

Jemma

jemma_borland · March 21, 2018, 4:11pm

Hi Gully

<o:p> </o:p>

I think the following line will do what you?re after, as Karan originally suggested:

0!select Total:sum oqm by DateTime,BidOrAsk from update oqm:Orders*Quantity from tab<o:p></o:p>

<o:p> </o:p>

If you also want Price and Ltp in your output, you can add first price, first Ltp to your select statement like so:<o:p></o:p>

<o:p> </o:p>

0!select first Price,first Ltp,Total:sum oqm by DateTime,BidOrAsk from update oqm:Orders*Quantity from tab<o:p></o:p>

<o:p> </o:p>

Here?s sample input and output which seem to match your requirements:<o:p></o:p>

<o:p> </o:p>

q)tab:(DateTime:(10#.z.z-1%24),10#.z.z;Price:273.15,273.1,273.05,273,272.95,273.2,273.3,273.35,273.4,273.45,273.15,273.1,273.05,273,272.95,273.2,273.3,273.35,273.4,273.45;BidOrAsk:raze 5#‘(0;1;0;1);Quantity:20#10;Orders:raze 5#’(1;2;4;5);Ltp:20#273.05)<o:p></o:p>

q)tab<o:p></o:p>

DateTime Price BidOrAsk Quantity Orders Ltp<o:p></o:p>

--------------------------------------------------------------<o:p></o:p>

2018.03.21T15:00:39.118 273.15 0 10 1 273.05<o:p></o:p>

2018.03.21T15:00:39.118 273.1 0 10 1 273.05<o:p></o:p>

2018.03.21T15:00:39.118 273.05 0 10 1 273.05<o:p></o:p>

2018.03.21T15:00:39.118 273 0 10 1 273.05<o:p></o:p>

2018.03.21T15:00:39.118 272.95 0 10 1 273.05<o:p></o:p>

2018.03.21T15:00:39.118 273.2 1 10 2 273.05<o:p></o:p>

2018.03.21T15:00:39.118 273.3 1 10 2 273.05<o:p></o:p>

2018.03.21T15:00:39.118 273.35 1 10 2 273.05<o:p></o:p>

2018.03.21T15:00:39.118 273.4 1 10 2 273.05<o:p></o:p>

2018.03.21T15:00:39.118 273.45 1 10 2 273.05<o:p></o:p>

2018.03.21T16:00:39.118 273.15 0 10 4 273.05<o:p></o:p>

2018.03.21T16:00:39.118 273.1 0 10 4 273.05<o:p></o:p>

2018.03.21T16:00:39.118 273.05 0 10 4 273.05<o:p></o:p>

2018.03.21T16:00:39.118 273 0 10 4 273.05<o:p></o:p>

2018.03.21T16:00:39.118 272.95 0 10 4 273.05<o:p></o:p>

2018.03.21T16:00:39.118 273.2 1 10 5 273.05<o:p></o:p>

2018.03.21T16:00:39.118 273.3 1 10 5 273.05<o:p></o:p>

2018.03.21T16:00:39.118 273.35 1 10 5 273.05<o:p></o:p>

2018.03.21T16:00:39.118 273.4 1 10 5 273.05<o:p></o:p>

2018.03.21T16:00:39.118 273.45 1 10 5 273.05<o:p></o:p>

<o:p> </o:p>

q)0!select first Price,first Ltp,Total:sum oqm by DateTime,BidOrAsk from update oqm:Orders*Quantity from tab<o:p></o:p>

DateTime BidOrAsk Price Ltp Total<o:p></o:p>

-------------------------------------------------------<o:p></o:p>

2018.03.21T15:00:39.118 0 273.15 273.05 50<o:p></o:p>

2018.03.21T15:00:39.118 1 273.2 273.05 100<o:p></o:p>

2018.03.21T16:00:39.118 0 273.15 273.05 200<o:p></o:p>

2018.03.21T16:00:39.118 1 273.2 273.05 250<o:p></o:p>

<o:p> </o:p>

Please let me know if this helps.

<o:p> </o:p>

Best regards,

Jemma

<o:p> </o:p>

From: Gully G
Sent: 21 March 2018 11:43
To: Kdb+ Personal Developers
Subject: [personal kdb+] Re: Find Aggregated Value within Column..

<o:p> </o:p>

I dont know why the my previous comment not showing the example.<o:p></o:p>

<o:p> </o:p>

@Alex please have a look at this example. I think eveyone is having a confusion as the previous example was showing only one datatime.<o:p></o:p>

<o:p> </o:p>

DateTime Price BidOrAsk Quantity Orders Ltp<o:p></o:p>

--------------------------------------------------------------<o:p></o:p>

2018.03.21T02:06:34.203 273.15 0 10 1 273.05<o:p></o:p>

2018.03.21T02:06:34.203 273.1 0 10 1 273.05<o:p></o:p>

2018.03.21T02:06:34.203 273.05 0 10 1 273.05<o:p></o:p>

2018.03.21T02:06:34.203 273 0 10 1 273.05<o:p></o:p>

2018.03.21T02:06:34.203 272.95 0 10 1 273.05<o:p></o:p>

2018.03.21T02:06:34.203 273.2 1 10 2 273.05<o:p></o:p>

2018.03.21T02:06:34.203 273.3 1 10 2 273.05<o:p></o:p>

2018.03.21T02:06:34.203 273.35 1 10 2 273.05<o:p></o:p>

2018.03.21T02:06:34.203 273.4 1 10 2 273.05<o:p></o:p>

2018.03.21T02:06:34.203 273.45 1 10 2 273.05<o:p></o:p>

<o:p> </o:p>

2018.03.21T02:06:35.203 273.15 0 10 4 273.05<o:p></o:p>

2018.03.21T02:06:35.203 273.1 0 10 4 273.05<o:p></o:p>

2018.03.21T02:06:35.203 273.05 0 10 4 273.05<o:p></o:p>

2018.03.21T02:06:35.203 273 0 10 4 273.05<o:p></o:p>

2018.03.21T02:06:35.203 272.95 0 10 4 273.05<o:p></o:p>

2018.03.21T02:06:35.203 273.2 1 10 5 273.05<o:p></o:p>

2018.03.21T02:06:35.203 273.3 1 10 5 273.05<o:p></o:p>

2018.03.21T02:06:35.203 273.35 1 10 5 273.05<o:p></o:p>

2018.03.21T02:06:35.203 273.4 1 10 5 273.05<o:p></o:p>

2018.03.21T02:06:35.203 273.45 1 10 5 273.05<o:p></o:p>

<o:p> </o:p>

The output looks like this<o:p></o:p>

DateTime Price BidOrAsk Ltp Total<o:p></o:p>

--------------------------------------------------------------<o:p></o:p>

2018.03.21T02:06:34.203 273.15 0 273.05 (10*1)+(10*1)+(10*1)+(10*1)+(10*1)=50<o:p></o:p>

2018.03.21T02:06:34.203 273.1 1 273.05 (10*2)+(10*2)+(10*2)+(10*2)+(10*2)=100<o:p></o:p>

<o:p> </o:p>

2018.03.21T02:06:35.203 273.15 0 273.05 (10*4)+(10*4)+(10*4)+(10*4)+(10*4)=200<o:p></o:p>

2018.03.21T02:06:35.203 273.1 1 273.05 . (10*5)+(10*5)+(10*5)+(10*5)+(10*5)=250<o:p></o:p>

<o:p> </o:p>

Thanks,<o:p></o:p>

Gully<o:p></o:p>

<o:p> </o:p>

Topic		Views
kdb equivalent of pandas groupby Community Support kdb-and-q	2	November 22, 2021
kdb equivalent of pandas groupby Community Support imported , kdb-and-q	2	November 22, 2021
It's not the SQL I'm used to...or maybe I'm dumb. Community Support kdb-and-q	4	August 16, 2010
Heikin-Ashi Community Support kdb-and-q	2	March 24, 2016
Re: [personal kdb+] Aggregating Best Bid/Offer an Quantity By Time Community Support kdb-and-q	1	October 28, 2013

Find Aggregated Value within Column..

DateTime Price BidOrAsk Quantity Orders Ltp

and the final result looks like this: DateTime Price BidOrAsk Quantity Orders Ltp QO

Related topics

and the final result looks like this:
DateTime Price BidOrAsk Quantity Orders Ltp QO