counting results in sub-query

[stephenmoorhous]

I have a list of log lines which indicate an order has been placed and have a session id (sid) but no customer id (cid)
I also have a list of log lines which have a session id and a customer id

so
index=foo sourcetype=bar sid=123 orderplaced=1
index=foo sourcetype=zzz sid=123 cid=1 this=some
index=foo sourcetype=zzz sid=123 cid=1 this=random
index=foo sourcetype=zzz sid=123 cid=1 this=stuff
index=foo sourcetype=bar sid=123 orderplaced=1
index=foo sourcetype=zzz sid=123 cid=1 this=more
index=foo sourcetype=zzz sid=123 cid=1 this=random
index=foo sourcetype=zzz sid=123 cid=1 this=thing
index=foo sourcetype=bar sid=321 orderplaced=1
index=foo sourcetype=zzz sid=321 cid=2 this=etc
index=foo sourcetype=zzz sid=555 cid=3 this=etc

etc

what i want is a count of how many orders each customer has placecd so the above would produce
cid count
1 2
2 1
3 0

I have
index=foo sourcetype=zzz [search index=foo sourcetype=bar orderplaced=1 | fields sid] | stats count by cid
but this counts all the log lines in foo so cid=1 gets a count of 6

I'm sure there is a simple solution but can think of it

Thanks

[cmerriman · Accepted solution]

try this: index=foo sourcetype=zzz |join sid [search index=foo sourcetype=bar orderplaced=1 | fields sid orderplaced] |fillnull orderplaced value=0|stats values(cid) as cid sum(orderplaced) as orderplaced by sid|stats sum(orderplaced) as orders by cid