Count by values for two fields like a table

[nkotha]

I have events coming with two distinct key say "Key1" and "Key2". Expected value for these keys are 1 to 3. I want a table where I can see the count of each key for every possible value like...

Value | count(Key1) | count( Key2)
1 | 10 | 3
2 | 1 | 15
3 | 4 | 6

tried "| stats count(key1), count(key2) by value" but that doesn't work

[woodcock · Accepted solution]

This was another fun one! Like this (If confused, look at the results without each of the last 2 lines):

...  | multireport
        [ stats count AS "count(Key1)" BY Key1 ]
        [ stats count AS "count(Key2)" BY Key2 ]
     | eval Value=coalesce(Key1, Key2)
     | fields - Key* | stats first(*) AS * BY Value

[Richfez]

What does the example you provide | stats count(key1), count(key2) by value do that's incorrect? Can you provide a sample of 5 or 10 events and the actual results/totals you'd get using those 5 or 10 events?

Thanks - that would go a long ways toward helping everyone understand the issue, which would go a long ways toward getting some possible answers.