Can I sum the count of two columns from two different tables?

Question

I'm trying to add together the counts of two different tables and group them by the same variable

Here is what I have so far:

SELECT a.storenumber,
       Count (howmanytotal) AS total_counts_store
FROM   (
              SELECT month_counts.howmany,
                     new_counts.howmany) AS howmanytotal
from   (
                  SELECT     a.storenumber,
                             count (b.riid_) AS howmany
                  FROM       $b$ b
                  INNER JOIN $a$ a
                  ON         b.riid_=a.riid_
                  GROUP BY   a.storenumber) month_counts
FROM   (
                  SELECT     a.storenumber,
                             count (c.riid_) AS howmany
                  FROM       $c$ c
                  INNER JOIN $a$ a
                  ON         c.riid_=a.riid_
                  GROUP BY   a.storenumber) new_counts
ON month_counts.storenumber = new_counts.storenumber) theend

where I'm at now:

SELECT howmanytotal AS total_counts_store
FROM   (
              SELECT Count (howmany) AS howmanytotal)
FROM   (
              SELECT month_counts.howmany,
                     new_counts.howmany)
FROM   (
                  SELECT     a.storenumber,
                             count (b.riid_) AS howmany
                  FROM       $b$ b
                  inner join $a$ a
                  ON         b.riid_=a.riid_
                  GROUP BY   a.storenumber) month_counts
UNION
      (
                 SELECT     count (c.riid_) AS howmany
                 FROM       $c$ c
                 inner join $a$ a
                 ON         c.riid_=a.riid_
                 GROUP BY   a.storenumber) new_counts
ON month_counts.storenumber = new_counts.storenumber) ORDER BY $a$.storenumber

Getting this error: Error: java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected Please correct SELECT statement:

Answer 1

Join the subqueries:

select
  storenumber,
  month_counts.howmany as month_count,
  new_counts.howmany as new_count,
  month_counts.howmany + new_counts.howmany as total_count
from (...) month_counts
join (...) new_counts using (storenumber)
order by storenumber;

If it is possible for a storenumber to be missing from one of the subquery results, then outer join and use COALESCE or NVL to deal with the nulls. Here is a query with a full outer join, which is not available in MySQL, but in Oracle and many other DBMS.

select
  storenumber,
  month_counts.howmany as month_count,
  new_counts.howmany as new_count,
  nvl(month_counts.howmany, 0) + nvl(new_counts.howmany, 0) as total_count
from (...) month_counts
full outer join (...) new_counts using (storenumber)
order by storenumber;

Answer 2

Ending up using sum and union to complete. Thank you for your help.

SELECT storenumber,
       SUM(howmany) AS howmanytotal
FROM   (SELECT a.storenumber,
               Count (b.riid_) AS howmany
        FROM   $b$ b
               inner join $a$ a
                       ON b.riid_ = a.riid_
        GROUP  BY a.storenumber
        UNION
        SELECT a.storenumber,
               Count (c.riid_) AS howmany
        FROM   $c$ c
               inner join $a$ a
                       ON c.riid_ = a.riid_
        GROUP  BY a.storenumber)
GROUP  BY storenumber
ORDER  BY storenumber 

This gave me a list of store ids and how many active subscribers we have at each store (taken from two separate tables)