Counting and grouping same named column in different tables (mysql)

Question

Okay I have two tables: Table 1 looks like this:

id age gender
1   10   M
2   11   F
3   11   F

And Table 2 looks like this (same with different values):

id age gender
1  11   F
2  12   M
3  10   M

Now I want my final output to look like the following:

age count
10   2
11   3
12   1

What is the most efficient way to achieve this?

DO you want to know 'the most efficient way', or just 'a way'? — Strawberry, Jan 31 '13 at 22:04
Merge the tables via one of the methods in http://stackoverflow.com/questions/725556/how-can-i-merge-two-mysql-tables and then do a GROUP BY age — ryanbwork, Jan 31 '13 at 22:05
@Strawberry sorry for replying late, 'a way' and 'the most efficient way' :D — pewpewlasers, Feb 01 '13 at 06:54

score 6 · Accepted Answer · answered Jan 31 '13 at 22:13

6

You want to aggregate the union:

select age, count(*)
from (select id, age, gender from table1 union all
      select id, age, gender from table2
     ) t
group by age

answered Jan 31 '13 at 22:13

Gordon Linoff

hey thanks for your answer, works like a charm... if you have some time could you please check this out too? :D http://stackoverflow.com/questions/14641572/counting-and-grouping-same-named-column-in-different-tables-where-clause-from-o – pewpewlasers Feb 01 '13 at 07:31
1

@user1768337 . . . Whether you do the union/aggregation (as done here) or aggregation/union/aggregation is really a question of performance. For large data sets, I might be tempted to do the latter. – Gordon Linoff Feb 01 '13 at 15:11

echo_Me · Answer 2 · 2013-01-31T22:22:41.387

0

try this

select age ,count(age) count from table1 group by age
union
select age, count(age)  count from table2 group by age

edited Jan 31 '13 at 22:22

answered Jan 31 '13 at 22:09

echo_Me

2 Answers2