# Compute Count for each interval

I have query to compute space usage per account

``````select RAW sum(CASE WHEN ci.durationMinutes IS NOT VALUED THEN 0 ELSE ci.durationMinutes END)
from `bucket` as act
LEFT OUTER JOIN `bucket` as ci
on META(act).id = ci.token  and ci.type="catalog" AND ci.status="RECORDED"
where
act.ts = 32503680000
group by
META(act).id
``````

I need to figure out how to calculate number of accounts that are in specific interval based on space usage. I think this is possible using using CASE … WHEN construction (see below), but I couldn’t construct the exact query. Any ideas?

``````select duration, count(duration) from(
select RAW sum(CASE WHEN ci.durationMinutes IS NOT VALUED THEN 0 ELSE ci.durationMinutes END)
from `bucket` as act
LEFT OUTER JOIN `bucket` as ci
on META(act).id = ci.token  and ci.type="catalog" AND ci.status="RECORDED"
where
act.ts = 32503680000
group by
META(act).id) space
let duration = CASE WHEN space =< 15 THEN "dur15"
WHEN `dur` > 15 AND `dur` =< 30 THEN "dur30"
WHEN `dur` > 30 AND `dur` =< 45 THEN "dur45"
ELSE THEN "durOthers" END
group by duration;``````

your query is right if u replace `dur` with space. Also SUM(), AVG()s only considers numeric values and other types are ignored.

``````SELECT duration, COUNT(1) AS cnt
FROM (SELECT RAW SUM(ci.durationMinutes)
FROM `bucket` AS act
LEFT OUTER JOIN `bucket` AS ci ON META(act).id = ci.token AND ci.type="catalog" AND ci.status="RECORDED"
WHERE act.ts = 32503680000
GROUP BY META(act).id) AS d
LET duration = CASE WHEN d <= 15 THEN "dur15"
WHEN d > 15 AND d <= 30 THEN "dur30"
WHEN d > 30 AND d <= 45 THEN "dur45"
ELSE "durOthers" END
GROUP BY duration;``````