CODEWITHSUNDEEP
mysqlunion
stramin
stramin

Reputation: 2390

MySQL count results in a subquery with union

I have some queries with UNION, I want to count them and show it as a row in another query

Example: I have a table called "clients", they can buy on store1, store2 or store 3, I need to show their names and how many items they bought on a row called "sales"

SELECT name,COUNT(*) FROM(
    SELECT 1 FROM store1 WHERE store1.client=clients.id
    UNION
    SELECT 1 FROM store2 WHERE store2.client=clients.id
    UNION
    SELECT 1 FROM store3 WHERE store3.client=clients.id
) sales
FROM clients

If john bought 2 items from store 2 and 1 item from store 3, and mary didn't bought anything, The expected result is something like:

name | sales
------------
john | 3
mary | 0

But what I have is this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM clients'

This is another attempt using another select subquery:

SELECT name,(
    SELECT COUNT(*) FROM(
        SELECT 1 FROM store1 WHERE store1.client=clients.id
        UNION
        SELECT 1 FROM store2 WHERE store2.client=clients.id
        UNION
        SELECT 1 FROM store3 WHERE store3.client=clients.id
    ) xxxx -- (mandatory table name)
) sales
FROM clients

This give me this error:

Unknown column 'clients.id' in 'where clause'

I hope you can help me, Thank you in advance!

Upvotes: 0

Views: 1494

Answers (3)

xQbert
xQbert

Reputation: 35323

First union the tables then filter the results and counts...

You can't reference a field more than 1 level of separation. Since store1.client is 2 levels deep and clients is at level 0, you're more than 1 level of separation and this isn't allowed.

   SELECT C.name, count(1)
   FROM (
        SELECT 'Store1' as StoreTable, a.* FROM store1 a UNION
        SELECT 'Store2', b.* FROM store2 b UNION
        SELECT 'Store3', c.* FROM store3 c
    ) S
    RIGHT JOIN clients C
     on C.ID = S.Client       
    GROUP BY Name

This makes a few assumptions

  1. Data structure between each store table is the same
  2. You may need other data from stores table which is now accessible.

I might go one step further and just create a view called "Stores" joining all the "Stores" in a union to make other queries across stores simpler. and by hardcoding a "StoreTable name in the view you can always identify the source table if needed.

Upvotes: 2

jfneis
jfneis

Reputation: 2197

Almost there. Try something like:

SELECT c.name, sum(s.qt) as qt
from clients c
join (
    SELECT client.id, COUNT(*) FROM(
        SELECT client, sum(1) as qt FROM store1 group by client
        UNION
        SELECT client, sum(1) as qt FROM store2 group by client
        UNION
        SELECT client, sum(1) as qt FROM store3 group by client
    ) as sales s on (s.client = c.id)
group by c.name

I can be confusing something around MySQL and SQLServer, I'll test and let you know if there's something different.

Update: corrected the subquery, added sum and group_by and removed the where clause. It can have performance effects if you don't want to get all clients from all stores.

Upvotes: 0

Scott Dobbins
Scott Dobbins

Reputation: 294

 SELECT name,COUNT(*)  
 FROM clients  INNER JOIN
     (
     SELECT client as id, 1 FROM store1 
         UNION
     SELECT client as id, 1 FROM store2 
         UNION
     SELECT client as id, 1 FROM store3
     )

     as Stores on clients.id = Stores.id
     GROUP by name

Upvotes: 2

Related Questions