MySQL UNION COUNT -

MySQL UNION COUNT -

first allow me show tables info , explain problem.

mysql tables structure

create table more_tags ( tag_id int unsigned not null auto_increment, more_id int unsigned not null, user_id int unsigned not null, tag_name varchar(255) not null, primary key (tag_id), unique key (more_id, user_id, tag_name) ); create table tags( tag_id int unsigned not null auto_increment, another_id int unsigned not null, user_id int unsigned not null, tag_name varchar(255) not null, primary key (tag_id), unique key (another_id, user_id, tag_name) );

more_tads table data

tag_id tag_name 10 apple 192 apple 197 apple 203 apple 207 apple 217 news 190 bff 196 cape

tags table data

tag_id tag_name 1 apple 2 time 3 bff

okay asked similar question earlier. reason can't query count tags both tables counts tags 1 table in illustration below

current ouput

tag_id tag_name num 1 apple 5 2 bff 1 3 cape 1 4 time 1

but want grouping similar tags , count how many times found in tables in illustration below

desired output

tag_id tag_name num 1 apple 6 2 bff 2 3 cape 1 4 time 1

current mysql query

select * from(select `more_tags`.`tag_id`, `more_tags`.`tag_name`, count(`more_tags`.`tag_name`) 'num' `more_tags` inner bring together `users` on `more_tags`.`user_id` = `users`.`user_id` `users`.`active` null , `users`.`deletion` = '0' grouping `more_tags`.`tag_name` union( select `tags`.`tag_id`, `tags`.`tag_name`, count(`tags`.`tag_name`) 'num' `tags` inner bring together `users` on `tags`.`user_id` = `users`.`user_id` `users`.`active` null , `users`.`deletion` = '0' grouping `tags`.`tag_name`)) table_1 grouping `tag_name` order `tag_name` asc

considering counting on both parts of union, can sum both of these. grouping tag_name, do.

select *, sum('num') 'big_num' from( select `more_tags`.`tag_id`, `more_tags`.`tag_name`, count(`more_tags`.`tag_name`) 'num' ...same... grouping `more_tags`.`tag_name` union( select `tags`.`tag_id`, `tags`.`tag_name`, count(`tags`.`tag_name`) 'num' ...same... grouping `tags`.`tag_name` )# union 2nd part )# union table table_1 grouping `tag_name` # doing order `tag_name` asc

mysql count group-by union