Skip to content

Instantly share code, notes, and snippets.

@ZenCocoon
Created November 3, 2010 00:30
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ZenCocoon/660594 to your computer and use it in GitHub Desktop.
Save ZenCocoon/660594 to your computer and use it in GitHub Desktop.
With SQL, how to achieve the following goal
CREATE TABLE `users` (
`id` int(11) DEFAULT NULL,
`email` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`born_at` varchar(255) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `users` (`id`,`email`,`born_at`) VALUES ('1','same@example.com','1980-01-01');
INSERT INTO `users` (`id`,`email`,`born_at`) VALUES ('2','2@example.com','1980-01-02');
INSERT INTO `users` (`id`,`email`,`born_at`) VALUES ('3','3@example.com','1980-01-01');
INSERT INTO `users` (`id`,`email`,`born_at`) VALUES ('4','4@example.com','1980-01-03');
INSERT INTO `users` (`id`,`email`,`born_at`) VALUES ('5','same@example.com','1980-01-02');
INSERT INTO `users` (`id`,`email`,`born_at`) VALUES ('6','same@example.com','1980-01-02');
# Desired format
# count_with_similar_born_at_but_different_email born_at
2 1980-01-01
2 1980-01-02
1 1980-01-03
@ZenCocoon
Copy link
Author

Solution : SELECT born_at, COUNT(DISTINCT(email)) FROM users GROUP BY born_at

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment