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 INTO `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 to make a query: I have done this:

SELECT
`user1`,
COUNT(`id`) AS Total
FROM usuarios
WHERE (`user1` is not null)
GROUP BY `user1`
UNION
SELECT
`user2`,
COUNT(`id`) AS Total
FROM usuarios
WHERE (`user2` is not null)
GROUP BY `user2`


But the result is:

user1    total
juan      3
pepe      3
juan      2
pepe      2


I need to remove duplicate names, add up the total and this is the result:

user1    total
juan      5
pepe      5
SELECT users, COUNT(*) FROM (
    SELECT
    `user1` AS users
    FROM usuarios
    WHERE (`user1` is not null)
    UNION ALL
    SELECT
    `user2`
    FROM usuarios
    WHERE (`user2` is not null)
) sq 
GROUP BY users;


Do the grouping outside. The trick is to use UNION ALL instead of UNION. The latter one uses a distinct on the result. You don't have to include the id column in the subqueries, so you can later count it, because it's the primary key and doesn't allow null values anyway.