count and sum from multiple tables

[dharkangel]

I don't think my previous post is possible, so to get around it, I would like to know if this is possibly. I have multiple tables that have the same column names. I need to go to each table, count specific values in that column, sum these values and update a separate table with the values. Here's an example:

For table1:

Role
-----
EUM
EUM
ETM
DIR

For table2:

Role
______
ETM
ETM
DIR
EUM

For table3:
___________
DIR
EUM
EUM

Here is what I need to show in the NewTable:

EUM
____
5

ETM
_____
2

DIR
____
3

Thanks in advance

 

[r937]

yup, that's possible with simple COUNT(*) queries

you can also get an overall count like this:

select sum(subtotal) as final_count
  from (
select count(*) as subtotal
  from table1
union all    
select count(*) 
  from table2
union all    
select count(*) 
  from table3
) as all_counts

r937.com | rudy.ca