the following query does not work in vistadb where as the query right after that works fine. do i need to add anything to make it work or is it just not supported?
select count(*) / (select count(*) from table1) from table1 where id = 0
select count(*) / 2 from table 1 where id = 0
Offhand of course you shouldn't get inconsistent results, the best thing to do is open a support ticket to get something like this investigated. I'll convert this over to a ticket so we can help you out.
It looks like VistaDB is generally disallowing sub-queries in aggregate queries which is more aggressive than necessary--non-correlated sub-queries should be allowed. We'll need to add logic to distinguish the safe sub-queries from the unsafe correlated sub-queries which can't be allowed in an aggregate query.
In the mean time, it does appear to accept building simple expressions around the Count(*) aggregate--such as the second query--so you can work around the error by saving the sub-query result to a scalar variable. An equivalent query would be:
declare @count BigInt = (select count(*) from table1);
select count(*) / @count from table1 where id = 0