Group by column and display score from all tables in mysql

Question

I have two tables: Table 1 looks like this:

id type 1 bike 2 car 3 cycle 4 bike 

And Table2 looks like this:

 id type 1 bike 2 car 

I want my final output to look like this:

 type count_table1 count_table2 bike 2 1 car 1 1 cycle 1 0 

What is the most efficient way to do this in SQL?

Answer 1

A simple solution, there is no need for a complex join of tables and functions:

 SELECT type, MAX(count_table1) as count_table1, MAX(count_table2) as count_table2 FROM ( ( SELECT type, COUNT(*) AS count_table1, 0 AS count_table2 FROM Table1 GROUP BY type ) UNION ( SELECT type, 0 AS count_table1, COUNT(*) AS count_table2 FROM Table2 GROUP BY type) ) AS tmp GROUP BY type 

SQL Fiddle

Answer 2

You can try the following:

 SELECT t1.TYPE, ifnull(t1.COUNT1,0) CountTable1, ifnull(t2.COUNT2,0) CountTable2 FROM (SELECT TYPE, COUNT(*) count1 FROM TABLE1 GROUP BY TYPE)T1 LEFT JOIN (SELECT TYPE, COUNT(*) count2 FROM TABLE2 GROUP BY TYPE)T2 ON t1.TYPE = t2.TYPE UNION SELECT t1.TYPE, t1.COUNT1, t2.COUNT2 FROM (SELECT TYPE, COUNT(*) count1 FROM TABLE1 GROUP BY TYPE)T1 RIGHT JOIN (SELECT TYPE, COUNT(*) count2 FROM TABLE2 GROUP BY TYPE)T2 ON t1.TYPE = t2.TYPE 

See my working SQL Fiddle example.

Answer 3

 SELECT a.TYPE, COUNT(a.ID), COUNT(b.ID) FROM TABLE1 AS a LEFT OUTER JOIN TABLE2 AS b ON a.TYPE = b.TYPE GROUP BY a.TYPE UNION SELECT b.TYPE, COUNT(a.ID), COUNT(b.ID) FROM TABLE1 AS a RIGHT OUTER JOIN TABLE2 AS b ON a.TYPE = b.TYPE GROUP BY b.TYPE 

Answer 4

Another way to do it

 SELECT a.type, COALESCE(b.type_count, 0) count_table1, COALESCE(c.type_count, 0) count_table2 FROM ( SELECT type FROM Table1 UNION SELECT type FROM Table2 ) a LEFT JOIN ( SELECT type, COUNT(*) type_count FROM Table1 GROUP BY type ) b ON a.type = b.type LEFT JOIN ( SELECT type, COUNT(*) type_count FROM Table2 GROUP BY type ) c ON a.type = c.type 

Some explanation:

• Subquery a gets a separate list of types ( UNION will take care of this).
• Subquery b and c calculates the number of occurrences of the type in table1 and table2 respectively.
• And finally, the external SELECT combines everything together using LEFT JOIN and COALESCE to replace non-existent values ​​with 0 .

Output:

  |  TYPE |  COUNT_TABLE1 |  COUNT_TABLE2 |
 | ------- | -------------- | -------------- |
 |  bike |  2 |  1 |
 |  car |  1 |  1 |
 |  cycle |  1 |  0 |

Here is the SQLFiddle demo

Answer 5

 select type, count(*) from table1 group by type select type, count(*) from table2 group by type 

Getting the amount of each item

Answer 6

 SELECT a.`TYPE`, COALESCE(tbl1CNT,0) as tbl1CNT, COALESCE(tbl2CNT,0) as tbl2CNT FROM (SELECT `TYPE` FROM TABLE1 UNION SELECT `TYPE` FROM TABLE2) a LEFT JOIN (SELECT `TYPE`, COUNT(*) AS tbl1CNT FROM TABLE1 GROUP BY `TYPE`) b ON a.`TYPE` = b. `TYPE` LEFT JOIN (SELECT `TYPE`, COUNT(*) AS tbl2CNT FROM TABLE2 GROUP BY `TYPE`) c ON a.`TYPE` = c. `TYPE` 

Fiddle

Answer 7

 select T.type ,IFNULL(COUNT(T1.type),0) as 'count_table1' ,IFNULL(COUNT(T2.type),0) as 'count_table2' from Table1 as T1 left join Table2 as T2 on T2.id = T1.id group by T.type union select T.type ,IFNULL(COUNT(T1.type),0) as 'count_table1' ,IFNULL(COUNT(T2.type),0) as 'count_table2' from Table2 as T left join Table1 as T1 on T1.id = T2.id group by T.type