【SQL】複数の集計を1回のSQL文で行う方法を考える

 ■ はじめに

例えば...

全体数とその母数からさらに条件を付けてカウントしたい場合に
それをサブクエリを使わずに、一回で行う方法を考える

目次

【1】解決案
【2】サンプル

 【1】解決案

COUNT (条件 OR null)
でカウント可能。

 【2】サンプル

 * 全体数(count_all_student)とその内80点以上のデータ数を数える
 * DB : MySQL5.7

 テーブル

-- 学生
CREATE TABLE IF NOT EXISTS `student` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

-- 科目
CREATE TABLE IF NOT EXISTS `subject` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

-- 点数
CREATE TABLE IF NOT EXISTS `score` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `date` DATE NOT NULL,
    `student_id` INT(11) NOT NULL,
    `subject_id` INT(11) NOT NULL,
    `score` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    INDEX `FK_score_student` (`student_id`),
    INDEX `FK_score_subject` (`subject_id`),
    INDEX `date` (`date`),
    CONSTRAINT `FK_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`),
    CONSTRAINT `FK_score_subject` FOREIGN KEY (`subject_id`) REFERENCES `subject` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

 データ

-- 学生
REPLACE INTO `student` (`id`, `name`) VALUES
    (1, 'Mike'),
    (2, 'Tom'),
    (3, 'Sam');

-- 科目
REPLACE INTO `subject` (`id`, `name`) VALUES
    (1, 'Japanese'),
    (2, 'Math'),
    (3, 'Science'),
    (4, 'Social'),
    (5, 'English');

-- 点数
REPLACE INTO `score` (`id`, `date`, `student_id`, `subject_id`, `score`) VALUES
    (1, '2018-04-12', 1, 1, 40),
    (2, '2018-04-12', 2, 1, 43),
    (3, '2018-04-12', 3, 1, 86),
    (4, '2018-04-12', 1, 2, 21),
    (5, '2018-04-12', 2, 2, 43),
    (6, '2018-04-12', 3, 2, 32),
    (7, '2018-04-12', 1, 3, 54),
    (8, '2018-04-12', 2, 3, 87),
    (9, '2018-04-12', 3, 3, 76),
    (10, '2018-04-13', 1, 4, 65),
    (11, '2018-04-13', 2, 4, 98),
    (12, '2018-04-13', 3, 4, 87),
    (13, '2018-04-13', 1, 5, 76),
    (14, '2018-04-13', 2, 5, 45),
    (15, '2018-04-13', 3, 5, 84);

 データ確認

SELECT
 sb.name AS subject_name,
 st.name AS student_name,
 sc.score AS score
FROM
 score AS sc
INNER JOIN
 student AS st
ON
 st.id = sc.student_id
INNER JOIN
 subject AS sb
ON
 sb.id = sc.subject_id
ORDER BY
 sb.id, st.id

出力結果

subject_name | student_name | score
-------------+--------------+--------
Japanese     |         Mike |    40
Japanese     |          Tom |    43
Japanese     |          Sam |    86
-------------------------------------
Math         |         Mike |    21
Math         |          Tom |    43
Math         |          Sam |    32
-------------------------------------
Science      |         Mike |    54
Science      |          Tom |    87
Science      |          Sam |    76
-------------------------------------
Social       |         Mike |    65
Social       |          Tom |    98
Social       |          Sam |    87
-------------------------------------
English      |         Mike |    76
English      |          Tom |    45
English      |          Sam |    84

 SQL

SELECT
 sb.name AS subject_name,
 COUNT(*) AS count_all_student,
 COUNT(sc.score >= 80 OR null) AS count_high_score
FROM
 score AS sc
INNER JOIN
 student AS st
ON
 st.id = sc.student_id
INNER JOIN
 subject AS sb
ON
 sb.id = sc.subject_id
GROUP BY
 sc.subject_id

 出力結果

subject_name | count_all_student | count_high_score
-------------+-------------------+------------------
Japanese     |                 3 |               1
Math         |                 3 |               0
Science      |                 3 |               1
Social       |                 3 |               2
English      |                 3 |               1

 参考文献

https://qiita.com/kazu56/items/8d1118557a5eb670ce6d
http://d.hatena.ne.jp/tacohachi/20100206/p1

関連記事

過去直近データを取得するには
https://dk521123.hatenablog.com/entry/2016/01/05/234938
最新日付データを取得するには
https://dk521123.hatenablog.com/entry/2014/10/09/004105
期間が重なるデータを取得するには
https://dk521123.hatenablog.com/entry/2011/12/17/025502
順位付け・ランキング ~ RANK / DENSE_RANK ~
https://dk521123.hatenablog.com/entry/2012/08/15/225233
初めの数文字が一致してたら同一データと見なすSQLを考える
https://dk521123.hatenablog.com/entry/2018/04/11/225229