SQLで重複を検索し件数を取得したい！

重複数を出すのが、難しかったので備忘録。

動作環境

この記事はSQL FIDDLEでMySQL5.6、PostgreSQL9.6、SQLiteで動作確認しました。
下記文章は主にPostgreSQLで仕様で調べています。

SQLで重複を検索し件数を取得する方法

サンプルコードで実際に重複件数を確認してみます。

準備

以下の内容のテーブルを使用します。

ItemID	ItemName	Quantity	AREA
1	プリン	25	大阪
2	ゼリー	20	大阪
3	ゼリー	30	東京
4	プリン	15	大阪
5	プリン	60	東京
6	プリン	25	大阪
7	ゼリー	20	東京

上記テーブルの作成用コードです。

— CREATE文 item テーブル
CREATE TABLE item (
ItemID numeric(8, 0) unique not null,
ItemName character varying (20),
Quantity numeric(8, 0),
AREA character varying (20),
primary key(ItemID)
);
— itemテーブル用INSERT文
insert into item
values(0001, ‘プリン’, 25, ‘大阪’),
(0002, ‘ゼリー’, 20, ‘大阪’),
(0003, ‘ゼリー’, 30, ‘東京’),
(0004, ‘プリン’, 15,’大阪’),
(0005, ‘プリン’, 60, ‘東京’),
(0006, ‘プリン’, 25, ‘大阪’),
(0007, ‘ゼリー’, 20, ‘東京’);

１カラムで重複している箇所を取得

ItemNameのカラムで重複している件数を取得したいと思います。

SELECT
count(ItemName),
ItemName
FROM
item
GROUP BY
ItemName
HAVING count(ItemName)>1
;

■ 取得結果

プリンが4つゼリーが3つ重複していることがわかります。

count	itemname
4	プリン
3	ゼリー

count()については以下でまとめています。

count()で集計をとる | SQL超初心者の勉強 テーブルから必要な項目の数を数えるときにcount()を使います。しかし、どのように使うのかわからなかったので調べてみました。 ...

複数カラムで重複している箇所を取得

ItemNameとAREAがどちらも重複している箇所を取得します。

SELECT
count(*),
ItemName,
AREA
FROM
item
GROUP BY
ItemName,
AREA
HAVING count(*)>1
;

■ 取得結果

count(*)	ItemName	AREA
2	ゼリー	東京
3	プリン	大阪

おわり

HAVINGが便利そうですね。
データが一意でなければならない時に、一意でないデータを抽出するのに使ったりしました。