sql - MySQL - How to group by on two fields and count -

sql - MySQL - How to group by on two fields and count -

create table if not exists `usuarios` ( `id` int(11) not null auto_increment, `user1` varchar(255) default null, `user2` varchar(255) default null, primary key (`id`) ) engine=innodb default charset=utf8 auto_increment=1 ; insert `usuarios` (`id`, `user1`, `user2`) values (1, 'pepe', null), (2, 'pepe', 'juan'), (3, 'juan', null), (4, 'juan', null), (5, 'juan', 'pepe'), (6, null, 'pepe'), (7, 'pepe', 'juan');

i need create query: have done this:

select `user1`, count(`id`) total usuarios (`user1` not null) grouping `user1` union select `user2`, count(`id`) total usuarios (`user2` not null) grouping `user2`

but result is:

user1 total juan 3 pepe 3 juan 2 pepe 2

i need remove duplicate names, add together total , result:

user1 total juan 5 pepe 5

or, if less code thang...

select user,count(*) ( select user1 user usuarios union select user2 usuarios ) x user not null grouping user;

mysql sql