Forum Discussion
Query Help (minus - operator)
Looking for some query assistance, is this the correct forum?
I am still learning the query language, so bare with me.
I am trying to get the difference of the resulting count from two queries. I have tried several combinations and I can't seem to figure this out. Any assistance would be appreciated.
Hi,
You can use the following query:
let Table1 = WaaSDeploymentStatus | where UpdateCategory=="Feature" and TargetOSVersion !="1709" and DetailedStatus == "Update successful" | count as Count1 | extend dummy=1 ; let Table2 = WaaSDeploymentStatus | where UpdateCategory=="Feature" and TargetOSVersion=="1709" and DetailedStatus == "Update successful" | count as Count2 | extend dummy=1; Table1 | join kind= inner ( Table2 ) on dummy | extend FinalCount = Count1 - Count2 | project FinalCountFirst it is best to split the two queries into sperate ones by using let statement - https://docs.loganalytics.io/docs/Language-Reference/Query-statements/Let-statement
Also notice that you will always provide the Table name in both of them. Also you will always have where clause when you filter. Additionally to the results of both queries you will add dummy column with the same value: https://docs.loganalytics.io/docs/Language-Reference/Tabular-operators/extend-operator
That way you can use join operator to join the results into single row: https://docs.loganalytics.io/docs/Language-Reference/Tabular-operators/join-operator
Not that the different counts have different names so you can distinguish them and substract them. We are doing that subtraction in a separate column and at the end we only show that column.
4 Replies
- Works perfectly, thank you Stanislav
Thanks Stan for answering the question. Here is another way of doing it that avoids joins and multiple table scans by using countif function
WaaSDeploymentStatus| where UpdateCategory=="Feature" and DetailedStatus == "Update successful"| summarize count1709=countif(TargetOSVersion=="1709"), countOthers=countif(TargetOSVersion!="1709")| project diffCount=countOthers-count1709Thanks, Ketan Ghelani
Hi,
You can use the following query:
let Table1 = WaaSDeploymentStatus | where UpdateCategory=="Feature" and TargetOSVersion !="1709" and DetailedStatus == "Update successful" | count as Count1 | extend dummy=1 ; let Table2 = WaaSDeploymentStatus | where UpdateCategory=="Feature" and TargetOSVersion=="1709" and DetailedStatus == "Update successful" | count as Count2 | extend dummy=1; Table1 | join kind= inner ( Table2 ) on dummy | extend FinalCount = Count1 - Count2 | project FinalCountFirst it is best to split the two queries into sperate ones by using let statement - https://docs.loganalytics.io/docs/Language-Reference/Query-statements/Let-statement
Also notice that you will always provide the Table name in both of them. Also you will always have where clause when you filter. Additionally to the results of both queries you will add dummy column with the same value: https://docs.loganalytics.io/docs/Language-Reference/Tabular-operators/extend-operator
That way you can use join operator to join the results into single row: https://docs.loganalytics.io/docs/Language-Reference/Tabular-operators/join-operator
Not that the different counts have different names so you can distinguish them and substract them. We are doing that subtraction in a separate column and at the end we only show that column.
