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 for a quick glance.
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...