MySQL Union query duplicate/group by problem

Question

Just learning about UNION queries - what a timesaver - but having trouble with grouping.

I have the following statement:

(SELECT `shops`.shpUserAdded AS userName, count(`shops`.shpID) AS shpCount, "" AS prdCount FROM `shops` GROUP BY shpUserAdded ASC)
UNION
(SELECT `products`.prdUserAdded AS userName, "" AS shpCount, count(`products`.prdID) AS prdCount FROM `products` GROUP BY prdUserAdded ASC)

but this gives me the results from the first query followed by the results of the second query, without combining the usernames:

userName|merCount|prdCount 
        |       8|
Jane    |       1|
Derek   |       1|
James   |      22|
        |        |       3
Jane    |        |       4  
Derek   |        |       5
James   |        |      21

I tried (but obviously got #1248 - Every derived table must have its own alias):

SELECT userName, shpCount, prdCount FROM
((SELECT `shops`.shpUserAdded AS userName, count(`shops`.shpID) AS shpCount, "" AS prdCount FROM `shops` GROUP BY shpUserAdded ASC)
UNION
(SELECT `products`.prdUserAdded AS userName, "" AS shpCount, count(`products`.prdID) AS prdCount FROM `products` GROUP BY prdUserAdded ASC))
GROUP BY userName ASC

I can't seem to figure out naming the derived table(s) without wiping out the data from either the shpCount or prdCount column - any suggestions? I'm probably going to feel stupid but thereyago!

Solved it (really this time)! :)

SELECT Users.userName, Products.prdCount, Shops.shpCount FROM 
(
    (
        (SELECT `Shops`.shpAddU AS userName FROM `Shops` GROUP BY shpAddU ASC)
        UNION
        (SELECT `Products`.prdAddU AS userName FROM `Products` GROUP BY prdAddU ASC)
    ) AS Users
) 
LEFT JOIN 
(SELECT prdAddU AS userName, count(*) AS prdCount FROM `Products` GROUP BY prdAddU) as Products ON Users.userName = Products.userName
LEFT JOIN 
(SELECT shpAddU AS userName, count(*) AS shpCount FROM `Shops` GROUP BY shpAddU) as Shops ON Users.userName = Shops.userName

Answer 1

Not everything is a union -- in this case it's a join on the subqueries:

SELECT shpUserAdded AS userName, merCount, prdCount
FROM (SELECT shpUserAdded, count(*) AS shpCount FROM shops GROUP BY shpUserAdded ASC) AS s
JOIN (SELECT prdUserAdded, count(*) AS prdCount FROM products GROUP BY prdUserAdded ASC) AS p
ON (shpUserAdded = prdUserAdded);