Getting count for group using N1QL

SQL++
1

I have the following query:

    SELECT 
    e.data.platform.id as carId, 
    e.data.servicingDetail.aceDetail.deploymentId  as deploymentId, 
    e.data.servicingDetail.aceDetail.ruleExecution.ruleName as ruleName, 
    e.data.servicingDetail.aceDetail.ruleExecution.rulePackageName as rulePackageName
    FROM `ace_events` as e 
     WHERE e.type = "MatchFired" 
    AND e.data.servicingDetail.aceDetail.deploymentId="com.engineering:RulesUseCases:1.0.0" 
     AND e.data.platform.id="600000527" 
     AND e.time between "2020-08-03T00:00:00.698Z" AND "2020-09-03T05:00:00.698Z"

which produces a result like so:

[
  {
    "carId": "600000527",
    "deploymentId": "com.engineering:RulesUseCases:1.0.0",
    "ruleName": "walmart-DecisionTable_11",
    "rulePackageName": "com.engineering.rulesusecases"
  },
  {
    "carId": "600000527",
    "deploymentId": "com.engineering:RulesUseCases:1.0.0",
    "ruleName": "walmart-DecisionTable_11",
    "rulePackageName": "com.engineering.rulesusecases"
  },
  {
    "carId": "600000527",
    "deploymentId": "com.engineering:RulesUseCases:1.0.0",
    "ruleName": "walmart-DecisionTable_11",
    "rulePackageName": "com.engineering.rulesusecases"
  },
  {
    "carId": "600000527",
    "deploymentId": "com.engineering:RulesUseCases:1.0.0",
    "ruleName": "walmart-DecisionTable_11",
    "rulePackageName": "com.engineering.rulesusecases"
  }
]

I would like to reduce the duplicates but count the repeated members of the groups. My desired outcome would be something like this:

[
  {
    "carId": "600000527",
    "deploymentId": "com.engineering:RulesUseCases:1.0.0",
    "execCount": 3,
    "ruleName": "walmart-DecisionTable_13",
    "rulePackageName": "com.engineering.rulesusecases"
  },
  {
    "carId": "600000527",
    "deploymentId": "com.engineering:RulesUseCases:1.0.0",
    "execCount": 5,
    "ruleName": "walmart-DecisionTable_14",
    "rulePackageName": "com.engineering.rulesusecases"
  },
  {
    "carId": "600000527",
    "deploymentId": "com.engineering:RulesUseCases:1.0.0",
    "execCount": 2,
    "ruleName": "walmart-DecisionTable_11",
    "rulePackageName": "com.engineering.rulesusecases"
  }
]

basically count any entry in the result that has the same values for all carId, deploymentId, ruleName and rulePackageName.
How can I achieve this with one N1QL query?

2 
SELECT 
    e.data.platform.id as carId, 
    e.data.servicingDetail.aceDetail.deploymentId  as deploymentId, 
    e.data.servicingDetail.aceDetail.ruleExecution.ruleName as ruleName, 
    e.data.servicingDetail.aceDetail.ruleExecution.rulePackageName as rulePackageName,
    COUNT(1) AS execCount
    FROM `ace_events` as e 
     WHERE e.type = "MatchFired" 
    AND e.data.servicingDetail.aceDetail.deploymentId="com.engineering:RulesUseCases:1.0.0" 
     AND e.data.platform.id="600000527" 
     AND e.time between "2020-08-03T00:00:00.698Z" AND "2020-09-03T05:00:00.698Z"
GROUP BY e.data.platform.id , 
    e.data.servicingDetail.aceDetail.deploymentId, 
    e.data.servicingDetail.aceDetail.ruleExecution.ruleName,
    e.data.servicingDetail.aceDetail.ruleExecution.rulePackageName;
3

Thanks for the response, I am getting the same result I get when I use count(*), something like this:

[
  {
    "carId": "600000527",
    "deploymentId": "com.engineering:RulesUseCases:1.0.0",
    "execCount": 11,
    "ruleName": "walmart-DecisionTable_14",
    "rulePackageName": "com.engineering.rulesusecases"
  },
  {
    "carId": "600000527",
    "deploymentId": "com.engineering:RulesUseCases:1.0.0",
    "execCount": 11,
    "ruleName": "walmart-DecisionTable_11",
    "rulePackageName": "com.engineering.rulesusecases"
  },
  {
    "carId": "600000527",
    "deploymentId": "com.engineering:RulesUseCases:1.0.0",
    "execCount": 22,
    "ruleName": "walmart-DecisionTable_13",
    "rulePackageName": "com.engineering.rulesusecases"
  }
]

the counts are way off, should be less I think, the data I have should give small counts.
I should mention that the fields hierarchy are as follows carId > deploymentId > rulePackageName > ruleName. My main interest is to get the count of every unique rule and produce output similar to this:

[
  {
    "carId": "600000527",
    "deploymentId": "com.engineering:RulesUseCases:1.0.0",
    "execCount": 3,
    "ruleName": "walmart-DecisionTable_13",
    "rulePackageName": "com.engineering.rulesusecases"
  },
  {
    "carId": "600000527",
    "deploymentId": "com.engineering:RulesUseCases:1.0.0",
    "execCount": 5,
    "ruleName": "walmart-DecisionTable_14",
    "rulePackageName": "com.engineering.rulesusecases"
  },
  {
    "carId": "600000527",
    "deploymentId": "com.engineering:RulesUseCases:1.0.0",
    "execCount": 2,
    "ruleName": "walmart-DecisionTable_11",
    "rulePackageName": "com.engineering.rulesusecases"
  }
]
4

The count will be right. I would check your data.
Pick the combination where you think is wrong. Add that combination to WHERE clause and
select data and see if the count matches.

If you want reduce duplicates on some field or combination of fields
COUNT (DSITINCT expression)

5

Is there a way to count the distinct occurrences of all the fields together?
something like this?:
…
COUNT(distinct e.data.platform.id,
e.data.servicingDetail.aceDetail.deploymentId,
e.data.servicingDetail.aceDetail.ruleExecution.ruleName,
e.data.servicingDetail.aceDetail.ruleExecution.rulePackageName)
…

6

Once you have group by on all the fields. DISTINCT on those will be 1 for each group (i.e projecting 1 AS cnt)

construct single array or object and perform DISTINCT on that

COUNT (DISTINCT [a,b,c,d,e])
COUNT (DISTINCT {a,b,c,d,e})

7

had to add

, execCount

at the end of the group by clause and it seems be producing the right counts now. Do you know why that is? Worth mentioning that using count(*), count(1) and count(distinct e.data.servicingDetail.aceDetail.ruleExecution.ruleName) all give the same output.

8

Not sure about add , execCount. Post complete query.

9

that’s the thing, I ran a simple select and counted manually and the count is like so:

[
{
  "carId": "600000527",
  "deploymentId": "com.engineering:RulesUseCases:1.0.0",
  "execCount": 1,
  "ruleName": "walmart-DecisionTable_14",
  "rulePackageName": "com.engineering.rulesusecases"
},
{
  "carId": "600000527",
  "deploymentId": "com.engineering:RulesUseCases:1.0.0",
  "execCount": 1,
  "ruleName": "walmart-DecisionTable_11",
  "rulePackageName": "com.engineering.rulesusecases"
},
{
  "carId": "600000527",
  "deploymentId": "com.engineering:RulesUseCases:1.0.0",
  "execCount": 2,
  "ruleName": "walmart-DecisionTable_13",
  "rulePackageName": "com.engineering.rulesusecases"
}

]
this is the same result I get by adding the execCount. If instead I leave the execCount off from the group by I get this:

[
{
  "carId": "600000527",
  "deploymentId": "com.engineering:RulesUseCases:1.0.0",
  "execCount": 11,
  "ruleName": "walmart-DecisionTable_11",
  "rulePackageName": "com.engineering.rulesusecases"
},
{
  "carId": "600000527",
  "deploymentId": "com.engineering:RulesUseCases:1.0.0",
  "execCount": 22,
  "ruleName": "walmart-DecisionTable_13",
  "rulePackageName": "com.engineering.rulesusecases"
},
{
  "carId": "600000527",
  "deploymentId": "com.engineering:RulesUseCases:1.0.0",
  "execCount": 11,
  "ruleName": "walmart-DecisionTable_14",
  "rulePackageName": "com.engineering.rulesusecases"
}
]

I wonder why this is like this?

10

Please provide the queries, so that able to understand question properly.

Provide all the documents. It is not possible to give one time 2 , other time 22

carId =  "600000527" 
AND deploymentId = "com.engineering:RulesUseCases:1.0.0" 
AND ruleName =  "walmart-DecisionTable_13" 
AND rulePackageName =  "com.engineering.rulesusecases"