【エンジニア教材】MySQL編(4) GROUP BY,HAVINGについて ref: http://qiita.com/yamaguchi_takashi/items/7e53336d323672fc8031

file0.txt

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `name_kana` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `blood_type` varchar(10) DEFAULT NULL,
  `birthday` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `users` (`id`, `name`, `name_kana`, `email`, `gender`, `age`, `blood_type`, `birthday`)
VALUES
    (1,'大村郁恵','おおむらいくえ','oomuraikue@example.com','F',39,'B','1978/1/23'),
    (2,'片岡寿明','かたおかとしあき','kataokatoshiaki@example.com','M',21,'A','1995/4/21'),
    (3,'岡島慎之介','おかじましんのすけ','okajimashinnosuke@example.com','M',20,'O','1997/2/4'),
    (4,'中田英嗣','なかたひでつぐ','nakatahidetsugu@example.com','M',23,'AB','1994/1/3'),
    (5,'藤村涼子','ふじむらりょうこ','fujimuraryouko@example.com','F',33,'O','1984/1/28'),
    (6,'相川一輝','あいかわかずき','aikawakazuki@example.com','M',35,'A','1981/8/18'),
    (7,'野崎由美子','のざきゆみこ','nozakiyumiko@example.com','F',12,'O','2005/1/20'),
    (8,'井口愛子','いぐちあいこ','iguchiaiko@example.com','F',18,'B','1998/8/6'),
    (9,'小森愛','こもりあい','komoriai@example.com','F',30,'O','1986/7/17'),
    (10,'那須由美子','なすゆみこ','nasuyumiko@example.com','F',13,'A','2003/10/5');

file1.txt

mysql> SELECT COUNT(*) AS "人数" FROM users WHERE gender = "F";
+--------+
| 人数   |
+--------+
|      6 |
+--------+
1 row in set (0.00 sec)

file2.txt

mysql> SELECT gender,COUNT(*) AS "人数" FROM users GROUP BY gender;
+--------+--------+
| gender | 人数   |
+--------+--------+
| F      |      6 |
| M      |      4 |
+--------+--------+
2 rows in set (0.00 sec)

file3.txt

mysql> SELECT gender,blood_type,COUNT(*) AS "人数" FROM users GROUP BY gender,blood_type;
+--------+------------+--------+
| gender | blood_type | 人数   |
+--------+------------+--------+
| F      | A          |      1 |
| F      | B          |      2 |
| F      | O          |      3 |
| M      | A          |      2 |
| M      | AB         |      1 |
| M      | O          |      1 |
+--------+------------+--------+
6 rows in set (0.00 sec)

file4.txt

mysql> SELECT gender,blood_type,COUNT(*) AS "人数" FROM users GROUP BY gender,blood_type HAVING 人数 >= 2;
+--------+------------+--------+
| gender | blood_type | 人数   |
+--------+------------+--------+
| F      | B          |      2 |
| F      | O          |      3 |
| M      | A          |      2 |
+--------+------------+--------+
3 rows in set (0.00 sec)