Get total count apart from group by count in the same query

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”,
}

Aggregates based on different groups needs to be processed using different queries and combine in application.

You can try this if it solves your purpose.

SELECT t1.*, totalCount
FROM ( select RAW count(*)
       from testbucket
       where effTS BETWEEN '21-Dec-17 08.41.00'
             AND '21-Dec-17 08.50.00'
     ) AS totalCount
UNNEST (
         select transId, msgId, service, 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
       ) AS t1;

Thanks @vsr1. This works and seems to have resolved my issue.:grinning: