SQL Query to sum multiple count union queries

Question

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

Instead of union, you can just add the results of the 2 select statements, preventing a Merge Join performance hit in your execution plan: select (SELECT COUNT(col_x) as rowtotal FROM tablea) + (SELECT COUNT(col_y) FROM tableb) AS Answer — DaFi4, Jul 26 '19 at 05:21
Thanks alot DaFi for this alternative. Both solutions yield the same result. I timed both execution times as a matter of interest (using + method instead of the UNION method) to pull out totals from 50 separate columns, and using the + method takes twice as long to finish running (42secs vs. 84 secs) - neither ideal for pushing to a webpage following a search query! — Ten Letters, Jul 26 '19 at 06:12
hi ten letters, thanks for the update. if speed is so important, and it sounds like it is, you might want to check the query execution plan to find out how to make it even faster. perhaps an index (or many) is missing — DaFi4, Jul 26 '19 at 06:20

score 3 · Accepted Answer · answered Jul 26 '19 at 05:14

3

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

answered Jul 26 '19 at 05:14

juergen d

201,996
37
293
362

score 1 · Answer 2 · answered Jul 26 '19 at 11:26

1

You can use +:

SELECT ( (SELECT COUNT(col_x) FROM tablea) + 
         (SELECT COUNT(col_y) FROM tableb)
       )

Or a cross join:

SELECT a_cnt + b_cnt
FROM (SELECT COUNT(col_x) as a_cnt FROM tablea) a CROSS JOIN
     (SELECT COUNT(col_y) as b_cnt FROM tableb) b;

Do not use UNION! It removes duplicates, so your sum may be off. You can use UNION ALL, but you have alternatives.

answered Jul 26 '19 at 11:26

Gordon Linoff

1,242,037
58
646
786

Cheers Gordon - I will try the cross join solution to see if I can cut down processing time. Thank you! – Ten Letters Jul 27 '19 at 08:05
@TenLetters . . . All these will have the same processing time as far as you can measure it. – Gordon Linoff Jul 27 '19 at 12:29

score 0 · Answer 3 · answered Jul 26 '19 at 05:14

0

Try this

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

answered Jul 26 '19 at 05:14

Fahmi

37,315
5
22
31

score 0 · Answer 4 · answered Jul 26 '19 at 05:21

You don;t need to give alias on table, instead it is actually saying to provide alias for column name.

WITH total as(
SELECT COUNT(col_x) as total FROM tablea    --- it is necessary over here
UNION
SELECT COUNT(col_y) as total  FROM tableb     --- it is not mandatory to give alias on second statement of UNION
) SELECT SUM(rowtotal) as total from total;

SQL Query to sum multiple count union queries

4 Answers4