distinct select from 2 databases I have 2 databases with the same structure massfacturation and massfacturation_old and I want to simply combine 2 queries to extract a count like:

&gt; select count(distinct(sciper)) as usercount, date_format(`date`, "%Y") as year from massfacturation_old.jobs group by year;
+-----------+------+
| usercount | year |
+-----------+------+
|       160 | 2017 |
|       262 | 2018 |
|       165 | 2019 |
+-----------+------+
3 rows in set (0.00 sec)


and on the new one

&gt; select count(distinct(sciper)) as usercount, date_format(`date`, "%Y") as year from massfacturation.jobs group by year;
+-----------+------+
| usercount | year |
+-----------+------+
|       170 | 2019 |
+-----------+------+
1 row in set (0.01 sec)


The idea is to end up with one result for 2017, 2018, 2019, combining both results.

Solution

Ended up with:

&gt; select count(distinct(sciper)), year FROM (SELECT sciper, date_format(`date`, "%Y") as year FROM massfacturation.jobs UNION SELECT sciper, date_format(`date`, "%Y") as year FROM massfacturation_old.jobs) AS j GROUP BY year;
+-------------------------+------+
| count(distinct(sciper)) | year |
+-------------------------+------+
|                     160 | 2017 |
|                     262 | 2018 |
|                     188 | 2019 |
+-------------------------+------+
3 rows in set (0.00 sec)


Thanks !
Use sub-query, consider the two outputs are temp1 and temp2:

SELECT
    sum( usercount ),
    `year` 
FROM
    ( SELECT * FROM  temp1  UNION ALL SELECT * FROM  temp2  ) AS temp1 
GROUP BY
    `year`


Edit   As @ypercubeᵀᴹ mentioned in the comment, UNION ALL will avoid removing the duplicated rows from temp1 and temp2.



Also if your tb and tb_old use the same sciper for the same user,
you may need to UNION before GROUP BY

SELECT
    `year`,
    count( sciper ) AS usercount
FROM
    ( SELECT sciper, YEAR ( `date` ) AS `year` FROM massfacturation_old 
        UNION 
        SELECT sciper, YEAR ( `date` ) AS `year` FROM massfacturation ) AS temp1 
GROUP BY
    `year`


In the above query, for every year and sciper combination, the temp1 will only have one row.