Counting matching values in multiple tables in MySQL

I have 6 vendor "inventory" databases that I have matched/not matched to a master database. If a unique ID was found in the inventory that was also in the master, we put the word 'Match' in a column we created called Status. If the master did not have a matching unique ID, we put 'No Match' in the Status column. We have done this for each vendor inventory database. I want to keep a running count of the amount of unique ID's that have 'Match' and 'No Match' to look at fo开发者_如何学Cr a quick glance.

For example:

Results that combine all 6 inventory databases:

Inventory total: 20000 'Match' total: 14000 'No Match' total: 6000

---

This may not be the best way, nor is it tested, but here's what I came up with.

SELECT SUM(1) AS inventory_total, 
   SUM(CASE status WHEN 'Match' THEN 1 ELSE 0 END) AS match_total,
   SUM(CASE status WHEN 'No Match' THEN 1 ELSE 0 END) AS no_match_total
FROM tbl
WHERE tbl_id IN (
    SELECT MAX(tbl_id)
    FROM tbl
    GROUP BY fkey_id
);

I'm not entirely sure if that's what you're after or not, but i hope it helps.

---

Would this work?

SELECT 
    COUNT(*) AS InventoryTotal,
    SUM(CASE WHEN STATUS = 'Match' THEN 1 ELSE 0 END) AS MatchTotal,
    SUM(CASE WHEN STATUS = 'No Match' THEN 1 ELSE 0 END) AS NoMatchTotal
FROM
    Table

---

SELECT COUNT(*) as [inventory total],
    SUM(CASE status WHEN 'Match' THEN 1 ELSE 0 END) AS [match total]
    SUM(CASE status WHEN 'No Match' THEN 1 ELSE 0 END) AS [no match total]
FROM db1.table UNION ALL db2.table UNION ALL db3.table
               UNION ALL db4.table UNION ALL db5.table UNION ALL db6.table

If I understand you correctly...