Try to calculate percentage but subquery returns null

SQL++
1

Hi everyone,

Im try to calculate percentages of browsers from visitor document.
my query
select browser as b, (count(browser) * 100 / (select count(*) from hbe h where @doctype = ‘visitor’)) as ratio from hbe where @doctype = ‘visitor’ group by browser

sample data:
{
“@doctype”: “visitor”,
“browser”: “Chrome”,
“deviceType”: “Desktop”,
“hotelId”: “90a37a0e-d0d0-411e-94a1-7f249ef79350”,
“id”: “0679fa12-f1d5-4d97-b054-952151885076”,
“language”: “TR”,
“operatingSystem”: “Windows 10”,
“resolution”: “1920x1080”,
“visitTime”: “2020-05-13T14:10:07.2873329”
}

response
{
“b”: “Chrome”,
“ratio”: null
}
can you help me :slight_smile: thanks.

2 
select 
      browser as b, 
      (count(browser) * 100 / (select  RAW count(*) 
                              from hbe h 
                              where  `@doctype`  = ‘visitor’)[0]
      ) as ratio 
from hbe 
where  `@doctype`  = ‘visitor’ group by browser
3

Hi thank you for your answer. its worked. can you tell me whats “[0]” that mean and my solution why doesn’t worked.

4

SUBQUERY returns array of documents. [0] means 0th element of ARRAY.
SUBQUERY projects OBJECTS. When projecting single element RAW project as value vs object of key:value

1 Like