Kusto - Join two tables and count keys from first table and second table on every record from first table

user_17506482 0
Viewed 125

Need to Join two tables and count key from first table and second table on every record from first table

let T = datatable(TId:int, TName:string, Tkey:string)
[
   1, "A", "xyz",
   2, "B", "xyz",
   3, "C", "yza",
];
let u = datatable(UId:int, UName:string, Ukey:string)
[
   1, "bla1", "xyz",
   2, "bla2", "xyz",
   30, "bla3", "xyz",
   12, "bla5", "xyz",
   80, "bla9", "xyz",
   4, "bla11", "cde",
   55, "bla12", "yza",
   96, "bla21", "yza",
];

Expected result

enter image description here

kusto-explorer summarize kql azure-data-explorer
1 Answers
let T = datatable(TId:int, TName:string, Tkey:string)
[
   1, "A", "xyz",
   2, "B", "xyz",
   3, "C", "yza",
];
let u = datatable(UId:int, UName:string, Ukey:string)
[
   1, "bla1", "xyz",
   2, "bla2", "xyz",
   30, "bla3", "xyz",
   12, "bla5", "xyz",
   80, "bla9", "xyz",
   4, "bla11", "cde",
   55, "bla12", "yza",
   96, "bla21", "yza",
];
let T_sum = T | summarize TkeyCount = count() by Tkey;
let u_sum = u | summarize UkeyCount = count() by Ukey;
T
| extend Ukey = Tkey
| lookup kind=leftouter T_sum on Tkey
| lookup kind=leftouter u_sum on Ukey
TId TName Tkey Ukey TkeyCount UkeyCount
1 A xyz xyz 2 5
2 B xyz xyz 2 5
3 C yza yza 1 2

Fiddle

user_63364790
Related