I'm stuck forming a MySQL query. I have 6 tables with similar columns. Before you ask they need to be updated separately and deleted; together with the quantity of the data.

So to get the sum from multiple tables, I use SELECT SUM(x) FROM (SELECT COUNT(value) as x etc)

To get one unique id I use DISTINCT(value). Now I need to combine them into a 2 column query, so I have a unique value and count.

My data looks like this for example form 1 table (multiply by 6):

```
    till_no  | fruit  | vegetables
    123      | apple  |  null
    123      | apple  | carrot
    125      | apple  | pear
    124      | apple  | null

```

What I want is 123|2, 124|1, 125|1

```
    SELECT DISTINCT(y), SUM(a) FROM (
      (SELECT till_no as y, (SELECT COUNT(till_no)  FROM Shop1) as a FROM Shop1 c)
      UNION (SELECT till_no as y, (SELECT COUNT(_till_no)  FROM Shop2) 
           as a FROM Shop2 c)
       ) multipleTables

```