CODEWITHSUNDEEP
mysql
Anthony
Anthony

Reputation: 95

SQL merge results and count from unions

I have 3 tables holding Operating system names, each table (client1, client2, client3) has a column with the same name as the table, with records listing Operating systems. Like so

client1 table as example

id          client1 <--same column name as the table                                                                                                                 
---------------------
1           Windows XP                                                                                                               
2           Windows Vista                                                                                                            
3           Windows 7 x32                                                                                                            
4           Windows 7 x64
...         ...                                                                                                
16          Not Applicable

These tables are used to populate a php drop down when the user is selecting results to enter in to the master table.

The master table has columns with the same names as the tables (client1, client2, client3) among others that are not relevant for this query populated by the users selection on the php drop down.

master table record example

client1         client2         client3
----------------------------------------------------------------------------
Windows 10 x64  Not Applicable  Not Applicable

I would like results giving the coverage of the Operating systems overall, so far I have the query below which gives me the count of the operating systems per table 

SELECT a.client1, COUNT(b.client1)
FROM client1 a 
LEFT JOIN master b ON a.client1 = b.client1
GROUP BY a.client1

UNION ALL(

SELECT c.client2, COUNT(d.client2)
FROM client2 c 
LEFT JOIN master d ON c.client2 = d.client2
GROUP BY c.client2
)
UNION ALL (
SELECT e.client3, COUNT(f.client3)
FROM client3 e 
LEFT JOIN master f ON e.client3 = f.client3
GROUP BY e.client3
)

ORDER by client1 
;

This returns the below (only showing two Operating systems as to save space)

client1     COUNT(b.client1)
-----------------------------
Windows 7          1
Windows 7          2
Windows 7          0
Windows 7          0
Windows 7          1
Windows 10         0
Windows 10         0
Windows 10         0
Windows 10         1
Windows 10         1

How can I go about getting the results returned like below so that only one row per OS name is shown and the count of how many times the OS is in the rows of the master table

OS                Count
-----------------------------
Windows 7          4
Windows 10         2

Upvotes: 0

Views: 866

Answers (1)

Thomas G
Thomas G

Reputation: 10216

alias properly the columns in the 3 unioned queries. Then make a nested query out of it, group by client and SUM your counts

SELECT client, SUM(cnt)
FROM
(

    SELECT a.client1 AS client, COUNT(b.client1) AS cnt
    FROM client1 a 
    LEFT JOIN master b ON a.client1 = b.client1
    GROUP BY a.client1

    UNION ALL(

    SELECT c.client2  AS client, COUNT(d.client2) AS cnt
    FROM client2 c 
    LEFT JOIN master d ON c.client2 = d.client2
    GROUP BY c.client2
    )
    UNION ALL (
    SELECT e.client3  AS client, COUNT(f.client3) AS cnt
    FROM client3 e 
    LEFT JOIN master f ON e.client3 = f.client3
    GROUP BY e.client3
    )
) T
GROUP BY client

Upvotes: 1

Related Questions