Count union of two table that has subquery

Question

I have this working query. It has count with subquery.

SELECT 
  COUNT(*) AS total 
FROM
  (SELECT 
    COUNT(aset) 
  FROM
    `public_1` 
  WHERE `public_1`.`aset` NOT IN 
    (SELECT 
      asset_code 
    FROM
      application_detail 
    WHERE application_id = 6) 
    AND org_id = 7 
    AND status_id = 8 
  GROUP BY aset) t

now I need to union with different table and get the total from both table. This code could get count record but the value is incorrect.

SELECT 
  COUNT(*) AS total 
FROM
  (SELECT 
    COUNT(aset) 
  FROM
    `public_1` 
  WHERE `public_1`.`aset` NOT IN 
    (SELECT 
      asset_code 
    FROM
      application_detail 
    WHERE application_id = 6) 
    AND org_id = 7 
    AND status_id = 8 
  UNION
  SELECT 
    COUNT(aset) 
  FROM
    `public_2` 
  WHERE `public_2`.`aset` NOT IN 
    (SELECT 
      asset_code 
    FROM
      application_detail 
    WHERE application_id = 6) 
    AND org_id = 7 
    AND status_id = 8 
  GROUP BY aset) z

Please assist me in getting the query correct. Thanks in advance

The query you provide here, is returning count as 1? Hint: make sure outer select gets more than one row to count. — Ajay, Dec 07 '18 at 20:36
`UNION` eliminates duplicates. If both counts are the same, one will get dropped. You want `UNION ALL` here instead. Also, you probably want to `SUM` the two counts together in the outer query. — Joe Stefanelli, Dec 07 '18 at 20:39
Your queries should just be `SELECT COUNT(DISTINCT aset)` instead of using a subquery. — Barmar, Dec 07 '18 at 20:47
First of all, do tell us what you are trying to achieve. Posting a broken query without telling us what you are trying to achieve will not help us help you. — Eric, Dec 07 '18 at 20:55
@Eric I did explained it in my post above here "`now I need to union with different table and get the total from both table.`" — sg552, Dec 08 '18 at 02:27

score 1 · Accepted Answer · answered Dec 07 '18 at 20:50

Use SELECT COUNT(DISTINCT aset) to get your counts, and then add them together.

SELECT t1.total + t2.total AS total
FROM (
    SELECT COUNT(DISTINCT aset) AS total
    FROM `public_1` 
    WHERE `public_1`.`aset` NOT IN 
        (SELECT 
          asset_code 
        FROM
          application_detail 
        WHERE application_id = 6) 
        AND org_id = 7 
        AND status_id = 8) AS t1
CROSS JOIN (
    SELECT COUNT(DISTINCT aset) AS total
    FROM `public_2` 
    WHERE `public_2`.`aset` NOT IN 
        (SELECT 
          asset_code 
        FROM
          application_detail 
        WHERE application_id = 6) 
        AND org_id = 7 
        AND status_id = 8) AS t2

Thank you very much. I never heard about `CROSS JOIN` before — sg552, Dec 08 '18 at 02:26

Count union of two table that has subquery

1 Answers1