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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy