get count from multiple tables

Question

I have 3 tables with a common 'type_id' column. its a foreign key from Table_type

Table_1 id,type_id,date... Table_2 id,type_id,date... Table_3 id,type_id,date...

Table_Type id, type_name, description

select type_id, count(*) from Table_1 GROUP BY type_id

gives the count from individual table.

i want to know how many times is a particular type used.

type_id, count(from 3 tables)
1      , 10
2      , 5
3 .    , 3

Answer 1

Use union all and group by:

select type_id, count(*)
from ((select type_id from table_1) union all
      (select type_id from table_2) union all
      (select type_id from table_3)
     ) t
group by type_id

Answer 2

Get the counts from each table, combine those queries with UNION, and add up the counts.

SELECT type_id, SUM(count)
FROM (
    SELECT type_id, COUNT(*) AS count
    FROM table_1
    UNION ALL
    SELECT type_id, COUNT(*) AS count
    FROM table_2
    UNION ALL
    SELECT type_id, COUNT(*) AS count
    FROM table_3
) AS x
GROUP BY type_id

If there are indexes on the type_id column, this query should be able to take advantage of them. And the UNION processes smaller tables because it combines after the grouping.