I have a set of documents listed below. The number of records for a given transId may be different. The requirement is to get the count of records by TransId and also to get the total number of records within a given time range. Is it possible to get both of these in the same N1QL query. Getting the count of records grouped by transId, service is straightforward, but I havent been able to get the total count. I looked at some of the posts on the forum, which suggested getting the sortCount from the metrics object, but that returns the total number of records after the group by is applied and hence does not serve the purpose.
select transId, msgId, service, effTS, count(*) as flowCount from testbucket where effTS BETWEEN ‘21-Dec-17 08.41.00’ AND ‘21-Dec-17 08.50.00’ group by transId, msgId, service order by effTS desc
{
“transId”: “123”,
“flow” : “A-B”,
“log”: “somelogtext1”,
“msgId”: “123msg”,
“effTS”: “21-Dec-17 08.45.42.778 AM”,
“service”: “service1”,
}
{
“transId”: “123”,
“flow” : “B-C”,
“log”: “somelogtext1”,
“msgId”: “123msg”,
“effTS”: “21-Dec-17 08.45.43.778 AM”,
“service”: “service1”,
}
{
“transId”: “567”,
“flow” : “A-B”,
“log”: “someothertext2”,
“msgId”: “567msg”,
“effTS”: “21-Dec-17 08.55.42.778 AM”,
“service”: “service2”,
}
{
“transId”: “567”,
“flow” : “B-C”,
“log”: “someothertext2”,
“msgId”: “567msg”,
“effTS”: “21-Dec-17 08.55.43.778 AM”,
“service”: “service2”,
}