SQL Query to sum multiple count union queries

I have a statement with following structure, and I’m trying to sum all the count results.

I have tried using the SUM function outside of the nested count queries combined using the same variable declaration for each of the union, but error says that I must give them separate aliases. However, if I do that, won’t I need to refer to these separately within the SUM function?

My SQL Code returning 2 rows:

SELECT COUNT(col_x) FROM tablea
UNION
SELECT COUNT(col_y) FROM tableb;

OUTPUT

64
10

Now when I try to SUM the answers I run into trouble:

WITH total as(
SELECT COUNT(col_x) FROM tablea as rowtotal
UNION
SELECT COUNT(col_y) FROM tableb as rowtotal
) SELECT SUM(rowtotal) from total;

The error is around using the variable ‘rowtotal’ twice. Surely there’s an easier way to this?

I simply want a sum of both values as:

OUTPUT
74

Answer

You don’t need window functions for that

select sum(cnt) as total
from
(
  SELECT COUNT(col_x) as cnt FROM tablea
  UNION
  SELECT COUNT(col_y) FROM tableb
) tmp