select count(*) from all tables

cog on 2004-10-28T15:28:30

in Oracle,

select (select count(*) from table_a) A, (select count(*) from table_b) B, ... from dual;

I'm pretty sure there are other ways, of course (looks like a friend of mine just came up with one that actually doesn't need the dual table; hence, it's portable).

I thought I'd store and share that information :-)


Another way

runrig on 2004-10-28T15:49:31

looks like a friend of mine just came up with one that actually doesn't need the dual table
Was it this?:
select count(*) A,
(select count(*)
from table2) B
from table1

Re:Another way

cog on 2004-10-28T16:14:39

I'm not sure, because he seems to be in the zone (where I should be too, probably), and I seem unable to ask him :-)

Hum... but I can't seem to put that code to work... :-|

Re:Another way

runrig on 2004-10-28T18:19:52

Hum... but I can't seem to put that code to work
Hmmm, works in Informix and SqlServer...is there an error in Oracle?

Re:Another way

cog on 2004-10-29T10:49:13

Yes, there's an error when running that in Oracle.

SQL> select count(*) A, (select count(*) from config) B from config;
select count(*) A, (select count(*) from config) B from config
*
ERROR at line 1:
ORA-00937: not a single-group group function


SQL>

That was also not the solution from my friend... I already asked him again... he's trying to remember :-)