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
– Strawberry, Commented Jan 31, 2013 at 22:04
Merge the tables via one of the methods in stackoverflow.com/questions/725556/… and then do a GROUP BY age — ryanbwork
– ryanbwork, Commented Jan 31, 2013 at 22:05
@Strawberry sorry for replying late, 'a way' and 'the most efficient way' :D — pewpewlasers
– pewpewlasers, Commented Feb 1, 2013 at 6:54

Gordon Linoff · Accepted Answer · 2013-01-31 22:13:10Z

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, 2013 at 22:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pewpewlasers Over a year ago

hey thanks for your answer, works like a charm... if you have some time could you please check this out too? :D stackoverflow.com/questions/14641572/…

Gordon Linoff Over a year ago

@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.

echo_Me · Accepted Answer · 2013-01-31 22:22:41Z

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, 2013 at 22:22

answered Jan 31, 2013 at 22:09

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related