October 23, 2014 at 6:14 am
Hello I made this:
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
October 23, 2014 at 6:26 am
How about:
SELECT COUNT(*), X.[UserName]
FROM
(SELECT
`user1` [UserName],
FROM usuarios
WHERE (`user1` is not null)
UNION ALL
SELECT
`user2` [UserName],
FROM usuarios
WHERE (`user2` is not null)) X
GROUP BY X.[UserName]
ORDER BY X.[UserName]
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 23, 2014 at 7:05 am
Your problem basically lies within the design of the table. If possible you should normalize the table and remove the 'duplicate' columns (User1, User2) and replace them with a single column (User). See more about normalization in Stairway to Database Design Level 9: Normalization[/url]. With a normilized table you can return the desired result with a single GROUP BY (and without a UNION).
October 24, 2014 at 3:44 am
Thank you very much for the answers
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply