How do I get sum of multiple SELECT COUNT statements?

March 23, 2022

I am using the following to return the count of multiple tables onto one row:

SELECT 
  (SELECT COUNT(Table1.Column1) 
   FROM Table1)                  AS Table1Column1Count,
  (SELECT COUNT(Table2.Column1) 
   FROM Table2)                  AS Table2Column1Count;

The above is working successfully, but my next step is to get a total of the results. I am asking if there is a way to return the sum value as an additional column of output within this SQL statement rather than running a separate SQL statement. And I need to do it using Standard SQL. In practice, it is actually 10+ SELECT COUNTS so I would like to avoid running that twice. I realize I can do that in the UI, and may end up doing that, but I wanted to explore this option first.

I have tried putting "+" between SELECT COUNT statements with AS TOTAL, and that works to get the total, but I am trying to get the individual table counts as well as the total to return on a single row of output

>Solution :

Try:

SELECT 
  (SELECT COUNT(Table1.Column1) FROM Table1) +
  (SELECT COUNT(Table2.Column1) FROM Table2)  AS Total;

Aliasses cannot be added so you will have to add (+) before adding aliasses.

When you want the individual totals, and the grand total

SELECT 
   Table1Column1Count, 
   Table2Column1Count,
   Table1Column1Count + Table2Column1Count as Total
FROM (
   SELECT 
     (SELECT COUNT(Table1.Column1) FROM Table1)  AS Table1Column1Count,
     (SELECT COUNT(Table2.Column1) FROM Table2)  AS Table2Column1Count
) subquery