グルーピングしたデータとは別のカラムを取得したいです。

Question

元のテーブル

AAA,BBB,CCC

1, ZZ, null
1, ZZ, YY
1, XZ, null
1, XZ, XX

　　↓

selectでとってきたい結果

AAA,CCC,count(*)

1, YY, 2
1, XX, 2

AAAとBBBでグルーピングして出力するのはAAAとCCC、
CCCがNULLのところはBBBの値が一緒の場合はそのCCCの値をあてがう感じです。
データがそのようになっていませんが、データは変更できません。

結果を以下のようにするにはどのようにすればよろしいでしょうか。
グルーピングだけならできるのですが。

Comments

[papinianus]

CCCがnullでない行がかならず一つだけ含まれるということですか？AAA,BBBでグルーピングした結果、CCCがnullばかりであったときや、nullでないものが2つ以上あったらどうなることを想定していますか？

[tomami]

ありがとうございます。AAAとBBBが同じものを一グループとして、CCCはどれか一つだけ必ず値あり、nullは複数あります。

Answer 1

相関副問合せで件数を取得しておき、それを集計します

select AAA,CCC, SUM(cnt)
from (
    select AAA,CCC,(select count(*) from テーブル where AAA=t1.AAA and BBB=t1.BBB) cnt
    from テーブル t1
    where CCC is not null
) iv
group by AAA,CCC

CCCはどれか一つだけ必ず値あり

ということなので、以下のようにするだけでも良いかも

select AAA,CCC,(select count(*) from テーブル where AAA=t1.AAA and BBB=t1.BBB) cnt
from テーブル t1
where CCC is not null

別解釈として、CCCが有効(Nullでない)なAAAとBBBのパターンの件数を求めるという事だと、

select AAA,CCC,(select count(*) from テーブル where AAA=V1.AAA and BBB=V1.BBB) cnt
from (
       select AAA,BBB,CCC
       from テーブル t1
       where CCC is not null
       group by AAA,BBB,CCC
) v1

Answer 2

素直に読むとこう？

• 元データ

create table tbl(AAA int,BBB varchar(20),CCC varchar(20) null);
insert into tbl values
(1, 'ZZ', null),
(1, 'ZZ', 'YY'),
(1, 'XZ', null),
(1, 'XZ', 'XX'); 

• 集計

select AAA,BBB,max(CCC) as CCC,count(*) as cnt from tbl group by AAA,BBB;

• BBBが気に入らないのであればサブクエリで

select AAA,CCC,cnt from 
(select AAA,BBB,max(CCC) as CCC,count(*) as cnt from tbl group by AAA,BBB) as sub;

Answer 3

BBBだけではcount(*)のユニーク性を担保できないはずだからこうかなと

select AB.AAA,
(select CCC from `table` where CCC is not null and AB.AAA = `table`.AAA and AB.BBB = `table`.BBB) as CCC,
AB.cnt from
(
  select AAA, BBB, count(*) as cnt
  from `table`
  group by AAA,BBB
) as AB

Comments

[papinianus]

出遅れてた

Answer 4

例えばこのようなデータがあった場合

AAA	BBB	CCC
1	ZZ	null
1	ZZ	YY
1	ZZ	XX

出力結果が一意に定まりませんよね。

AAA	CCC	count(*)
1	YY	1? 2?
1	XX	1? 2?

そこが限定できないと、望む形自体が伝わりませんよ。

---

AAA, BBB でグルーピングした結果、CCC がただ一つしか値がない（もしくはnull）なら、
これでいいのでは？

SELECT AAA, MAX(CCC), COUNT(*) FROM table1 GROUP BY AAA, BBB

Comments

[sazi]

確かに結果から見れば難しく考えなくても良いですね。
変にデータの正規化を考えながらアプローチしてしまった。