google-cloud-platformgoogle-cloud-spanner

GCP SPANNER: How to take count of records from multiple tables with single select statement

I need to take count from multiple tables in SPANNER with a single select statement. In traditional Oracle DB, I am used to below style of query.

Can someone suggest me how to do it in SPANNER.

select (SELECT count(1) FROM Table1 where Column1='ABC') as Count1, (SELECT count(1) FROM Table2 where Column2='ABC') as Count2, (SELECT count(1) FROM Table3 where Column3='ABC') as Count3, from dual;

I think we can't run use stored procedure in SPANNER for same. Please correct me if I am wrong.

Solution

  • The from dual part is very Oracle specific. You can use the following query with Spanner:

    select (select count(*) from foo) as c1, (select count(*) from bar) as c2