Adding and Multiplying COUNT () of Multiple Tables

Question

Is it possible to add and multiply the number of different tables, where id is the same? Imagine:

Table_1 Table_2 Table_3 id id id 1 1 1 1 2 2 2 2 3 3 2 3 3 2 3 3 3 3 

So, the end result will be this table with two columns:

 id (COUNT(Table_1.id) + 2*COUNT(Table_2.id) + 3*COUNT(Table_3.id)) 1 7 2 12 3 17 

Answer 1

I don’t know if I understood you correctly, but try,

 SELECT a.ID, a.aa + (2 * b.bb) + (3 * c.cc) FROM ( SELECT ID, COUNT(*) aa FROM table1 GROUP BY ID ) a LEFT JOIN ( SELECT ID, COUNT(*) bb FROM table2 GROUP BY ID ) b ON a.ID = b.ID LEFT JOIN ( SELECT ID, COUNT(*) cc FROM table3 GROUP BY ID ) c ON a.ID = c.ID 

SQLFiddle Demo

Answer 2

 SELECT id, counts_1.number + 2 * counts_2.number + 3 * counts_3.number FROM (SELECT id, COUNT(*) AS number FROM Table_1 GROUP BY id) AS counts_1 JOIN (SELECT id, COUNT(*) AS number FROM Table_2 GROUP BY id) AS counts_2 USING (id) JOIN (SELECT id, COUNT(*) AS number FROM Table_3 GROUP BY id) AS counts_3 USING (id) 

Please note that this solution requires that each identifier exists at least once in each of the tables, otherwise it will be left out of the result. This will require a FULL EXTERNAL ENTRANCE, which MySQL is incapable of. However, there are ways around this limitation .