How do I get the sum of the number of rows in this union?

Question

I have the following union query, which returns 8 for the first part of the union and 1 for the second. I'd like to know how to combine the totals so the result is 9.

Here is the query

SELECT COUNT(*)
FROM media
LEFT JOIN users ON users.id = media.userId
WHERE media.title LIKE '%braydo25%' OR media.description LIKE '%braydo25%' OR media.tags LIKE '%braydo25%' OR users.username LIKE 'braydo25'
UNION ALL
SELECT COUNT(*)
FROM reposts
LEFT JOIN media ON media.id = reposts.mediaId
LEFT JOIN users ON users.id = reposts.userId
WHERE media.title LIKE '%braydo25%' OR media.description LIKE '%braydo25%' OR media.tags LIKE '%braydo25%' OR users.username LIKE 'braydo25'

Why you don't search first , please avoid repeating same questions http://stackoverflow.com/questions/6685517/sum-totals-of-two-queries — Charaf JRA, Sep 10 '13 at 11:20

Gordon Linoff · Answer 1 · 2013-09-10T11:23:12.877

2

Sum the counts after the union:

select sum(cnt)
from (SELECT COUNT(*) as cnt
      FROM media
      LEFT JOIN users ON users.id = media.userId
      WHERE media.title LIKE '%braydo25%' OR media.description LIKE '%braydo25%' OR media.tags LIKE '%braydo25%' OR users.username LIKE 'braydo25'
      UNION ALL
      SELECT COUNT(*)
      FROM reposts
      LEFT JOIN media ON media.id = reposts.mediaId
      LEFT JOIN users ON users.id = reposts.userId
      WHERE media.title LIKE '%braydo25%' OR media.description LIKE '%braydo25%' OR media.tags LIKE '%braydo25%' OR users.username LIKE 'braydo25'
     ) t;

edited Sep 10 '13 at 11:23

answered Sep 10 '13 at 11:17

Gordon Linoff

1,242,037
58
646
786

Perfect, works well - but quick question, in this instance what does the t represent? at the end of the query – Braydon Batungbacal Sep 10 '13 at 11:20
@BraydonBatungbacal . . . `t` is a table alias. All subqueries need to have names in MySQL (and in certain other databases). – Gordon Linoff Sep 10 '13 at 11:23

How do I get the sum of the number of rows in this union?

1 Answers1