Skip to content

Instantly share code, notes, and snippets.

@ZenCocoon
Created November 3, 2010 00:30
Show Gist options
  • Select an option

  • Save ZenCocoon/660594 to your computer and use it in GitHub Desktop.

Select an option

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
Copy Markdown
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