SOLVED
Home

How can I summarize by case insensitive?

%3CLINGO-SUB%20id%3D%22lingo-sub-354155%22%20slang%3D%22en-US%22%3EHow%20can%20I%20summarize%20by%20case%20insensitive%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354155%22%20slang%3D%22en-US%22%3ECurrently%20I'm%20doing%20'summarize%20by%20tolower(name)'%20but%20I%20suspect%20that%20to%20be%20suboptimal%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-354155%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Data%20Explorer%20(Kusto)%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EKusto%20language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354242%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20summarize%20by%20case%20insensitive%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354242%22%20slang%3D%22en-US%22%3E%3CP%3EDepending%20on%20the%20kind%20of%20aggregation%20you're%20doing%2C%20it%20may%20be%20useful%20to%20first%20summarize%20by%20%3CEM%3Ename%3C%2FEM%3E%20and%20then%20summarize%20again%20by%20%3CEM%3Etolower(name)%3C%2FEM%3E%2C%20so%20that%20your%20query%20converts%20significantly%20fewer%20strings%20to%20lowercase.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDoing%20so%20is%20possible%2C%20for%20example%2C%20in%20the%20following%20case%2C%20with%20a%20%3CEM%3Ecount()%3C%2FEM%3E%20aggregation%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20style%3D%22font%3A%2010pt%20consolas%3B%22%3E%3CSPAN%20class%3D%22csl-function%22%3Edatatable%3C%2FSPAN%3E(s%3Astring)%0A%5B%0A%20%20%20%20%3CSPAN%20class%3D%22csl-string-literal%22%3E%22abc%22%3C%2FSPAN%3E%2C%0A%20%20%20%20%3CSPAN%20class%3D%22csl-string-literal%22%3E%22def%22%3C%2FSPAN%3E%2C%0A%20%20%20%20%3CSPAN%20class%3D%22csl-string-literal%22%3E%22ABC%22%3C%2FSPAN%3E%2C%0A%20%20%20%20%3CSPAN%20class%3D%22csl-string-literal%22%3E%22AbC%22%3C%2FSPAN%3E%2C%0A%20%20%20%20%3CSPAN%20class%3D%22csl-string-literal%22%3E%22def%22%3C%2FSPAN%3E%2C%0A%20%20%20%20%3CSPAN%20class%3D%22csl-string-literal%22%3E%22ABc%22%3C%2FSPAN%3E%0A%5D%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Esummarize%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-calculated-column%22%3Ec%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-function%22%3Ecount%3C%2FSPAN%3E()%20%3CSPAN%20class%3D%22csl-suboperator%22%3Eby%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-calculated-column%22%3Es%3C%2FSPAN%3E%0A%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Esummarize%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-calculated-column%22%3Ec%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-function%22%3Esum%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-calculated-column%22%3Ec%3C%2FSPAN%3E)%20%3CSPAN%20class%3D%22csl-suboperator%22%3Eby%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-calculated-column%22%3Es%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-function%22%3Etolower%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-calculated-column%22%3Es%3C%2FSPAN%3E)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
abergs
New Contributor
Currently I'm doing 'summarize by tolower(name)' but I suspect that to be suboptimal
1 Reply
Solution

Depending on the kind of aggregation you're doing, it may be useful to first summarize by name and then summarize again by tolower(name), so that your query converts significantly fewer strings to lowercase.

 

Doing so is possible, for example, in the following case, with a count() aggregation:

 

datatable(s:string)
[
    "abc",
    "def",
    "ABC",
    "AbC",
    "def",
    "ABc"
]
| summarize c = count() by s
| summarize c = sum(c) by s = tolower(s)