How to use a join on two statements?

Question

I have a statement that counts how many records that have attr3 = 1.

SELECT 'Dept' AS table_name, COUNT(*)
FROM ((select attr3 from table1) union all
      (select attr3 from table2)
     ) t
WHERE attr3 = '1';

Now I need to have another statement that does the same thing but counts how many records that have attr3 = 2.

SELECT 'Dept' AS table_name, COUNT(*)
    FROM ((select attr3 from table1) union all
          (select attr3 from table2)
         ) t2
    WHERE attr3 = '2';

Ideally the data will display in this format

Dept         count(*)
attr3 = 1          4
attr3 = 2          6

This method works if I do the statements separately, I just simply want to join them together. Can anyone help?

Answer 1

The group by will do the job.

SELECT 'Dept' AS table_name, COUNT(*)
FROM ((select attr3 from table1) union all
      (select attr3 from table2)
     ) t2
WHERE attr3 = '2'
group by dept;

http://www.w3schools.com/sql/sql_groupby.asp

Answer 2

A simple GROUP BY and WHERE attr3 in() should suffice.

SELECT Z.attr3 as Dept, count(*) as cnt
FROM (SELECT attr3 FROM table1
      UNION ALL
      SELECT attr3 FROM table2) Z
WHERE attr3 in ('1','2')
GROUP BY Z.attr3

Since attr3 defines the groups you want to count by, simply group by it and limit the results to your 1,2 after the union via a where clause.