LEFT JOIN doesn't includes groups with zero size

SQL++
1

I need to show count associated with each group even if the count is zero, however the query below shows shows non empty groups only. Could anyone please review the query.

select RAW count(1) from accountMgmt as act
left outer join accountMgmt as ci
on META(act).id = ci.accountToken
where
act.type=“account” and
act.packageName = “VR3” and
act.status = ‘ACTIVATED’ and
ci.type=“catalogItem” and
ci.status=“RECORDED”
group by
META(act).id;

2

I modified query to be:

select RAW count(1) from accountMgmt as act
left outer join accountMgmt as ci
on META(act).id = ci.accountToken
where
act.type=“account” and
act.packageName = “VR3” and
act.status = “ACTIVATED” and
(ci IS MISSING OR (ci.type=“catalogItem” and
ci.status=“RECORDED”))
group by
META(act).id;

I use following index for ci term:

CREATE INDEX index_catalogitems ON accountMgmt ( accountToken, status, durationMinutes )
WHERE ( type= “catalogItem” and (status = ‘RECORDED’ or status = ‘BOOKED’ ));

But now I get following error:

“No index available for ANSI join term ci”,

3 
SELECT RAW COUNT(ci)
FROM accountMgmt AS act
LEFT OUTER JOIN accountMgmt AS ci
ON META(act).id = ci.accountToken AND ci.type="catalogItem" AND ci.status="RECORDED"
WHERE act.type="account" AND act.packageName = "VR3" AND
act.status = "ACTIVATED" AND
GROUP BY META(act).id;

CREATE INDEX index_catalogitems ON accountMgmt ( accountToken, status, durationMinutes ) WHERE type="catalogItem";

Do not Use OR Clause in index WHERE clause

4

Now empty groups are in the resultset, but count() for these groups is 1 rather than 0

5

Use COUNT(ci) instead of COUNT(1)

1 Like