How to cumulative summary N1QL

SQL++
,
1

I have a requirement to calculate the cumulative summary

SELECT b.data.prodId,
b.data.ctDt,
COUNT(DISTINCT b.data.coId) AS activeCompCount
FROM `api_external` b
WHERE b.data.prodId IN ['a', 'b']
AND b.data.env = 'PRODUCTION' AND b.data.coId NOT IN ['testCompanyId1', 'testCompanyId2']
AND b.type = 'coProdSucCallCt'
AND b.data.ctDt BETWEEN 20240720 AND 20240724
GROUP BY b.data.prodId,
b.data.ctDt
HAVING COUNT(b.data.coId) > 0

[ 
{ 
prodId: a ,
ctDt: 20240720, 
activeCompCount : 1 
}, { 
prodId: a ,
ctDt: 20240721, 
activeCompCount : 4
}
, { 
prodId: a ,
ctDt: 20240722, 
activeCompCount : 3 
}
,{ 
prodId: a ,
ctDt: 20240723, 
activeCompCount : 2
} 
]

But we need cumulative summary
something like below. How do i write SQL ++ query

[ 
{ 
prodId: a ,
ctDt: 20240720, 
activeCompCount : 1 
}, { 
prodId: a ,
ctDt: 20240721, 
activeCompCount : 5 ( 1+4) 
}
, { 
prodId: a ,
ctDt: 20240722, 
activeCompCount : 8 (1+4+3 )
}
,{ 
prodId: a ,
ctDt: 20240723, 
activeCompCount : 10 (1+4+3+2 )
} 
]
2

@dh @vsr1 or any one who can help will be greatly appreciated,
Thanks a lot for ur time on this

3

You can try something like:

SELECT b.data.prodId
      ,b.data.ctDt
      ,SUM(COUNT(DISTINCT b.data.coId)) OVER win AS activeCompCount
FROM `api_external` b
WHERE b.data.prodId IN ['a', 'b']
AND b.data.env = 'PRODUCTION'
AND b.data.coId NOT IN ['testCompanyId1', 'testCompanyId2']
AND b.type = 'coProdSucCallCt'
AND b.data.ctDt BETWEEN 20240720 AND 20240724
GROUP BY b.data.prodId, b.data.ctDt
HAVING COUNT(b.data.coId) > 0
WINDOW win AS (ORDER BY b.data.prodId)

HTH.

4

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.