CODEWITHSUNDEEP
mysqlsql
user3447653
user3447653

Reputation: 4158

SQL query to iterate within the table

I am trying to write a query to obtain the below result from the table.

    Txn_desc    Key     Amt     Datetime                CId AId C_Id_1
    From        1234    210.91  2017-05-13 00:00:00.0   114 306 854
    From        -1     -210.91  2017-05-01 00:00:00.0   234 688 995
    To          9111    210.91  2017-05-01 00:00:00.0   234 688 995
    From        33500   210.91  2017-05-25 00:00:00.0   311 002 185
    To          1200   -210.91  2017-05-13 00:00:00.0   114 306 854
    To          78100  -210.91  2017-05-25 00:00:00.0   311 002 185
    From        4321   -210.91  2017-05-25 00:00:00.0   890 856 683
    To          11100   210.91  2017-05-25 00:00:00.0   890 856 683

Result: 

    From    To      Count
    1234    1200    1
    -1      9111    1
    33500   78100   1
    4321    11100   1

I tried with several versions but I believe i am missing something. Any suggestions would be appreciated.

Upvotes: 0

Views: 24

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use a self join.

select tf.key as from,tt.key as to, count(*)
from tbl tf
join tbl tt on tf.cid=tt.cid and tf.aid=tt.aid and tf.c_id_1=tt.c_id_1 and tf.txn_desc='From' and tt.txn_desc='To'
where tf.datetime=tt.datetime
group by tf.key,tt.key

Upvotes: 1

Related Questions