Search code examples
kdb

kdb how to have a conditional sum in select

How can I have a conditional sum in kdb, something like the following sql query

Select sum(qty > 0, qty, 0) as buy, sum(qty < 0, qty, 0) as sell from trades

I am looking for a way to do this without any where clause.

Solution

  • You can achieve this using a lambda function inline on your select statement:

    q)trades:([]sym:`a`b`c`d`e`f`g;qty:-34 -27 -11 50 19 -30 1)
q)select buy:sum {x where 0<x}[qty], sell:sum {x where 0>x}[qty] from trades
buy sell
--------
70  -102

    Better method (with conditional):

    q)select buy:sum ?[qty>0;qty;0], sell:sum ?[qty<0;qty;0] from trades
buy sell
--------
70  -102

    Using 'min' and 'max' operators:

    q)select buy:sum qty|0f,sell:sum qty&0f from trades
buy sell
--------
70  -102