SQLでCOUNTごとに取得するデータを変更したい

Question

データベースの環境はOracle 11gとなります。

前提・実現したいこと

現在下記のようなテーブルにて、カウント数ごとのデータを取得したいと考えています。
具体的には、1つの下記のようにテーブルにカラムがあります。

テストテーブル
...............
ユーザーID
KJコード
PGコード
ステータスコード
..................

CREATE TABLE TEST
(
  USER_ID VARCHAR(10)
 ,KJ_CODE NUMBER
 ,PG_CODE NUMBER
 ,STATUS_CODE NUMBER
);

このとき、ステータスコードには1～4のコードが登録されています。

INSERT INTO TEST(USER_ID, KJ_CODE,PG_CODE,STATUS_CODE ) VALUES ('0010',1234,1111,1) ;
INSERT INTO TEST(USER_ID, KJ_CODE,PG_CODE,STATUS_CODE ) VALUES ('0010',1234,1112,2) ;
INSERT INTO TEST(USER_ID, KJ_CODE,PG_CODE,STATUS_CODE ) VALUES ('0010',1234,1113,3) ;
INSERT INTO TEST(USER_ID, KJ_CODE,PG_CODE,STATUS_CODE ) VALUES ('0010',1234,1114,4) ;
INSERT INTO TEST(USER_ID, KJ_CODE,PG_CODE,STATUS_CODE ) VALUES ('0010',1234,1115,1) ;
INSERT INTO TEST(USER_ID, KJ_CODE,PG_CODE,STATUS_CODE ) VALUES ('0010',1234,1116,1) ;
INSERT INTO TEST(USER_ID, KJ_CODE,PG_CODE,STATUS_CODE ) VALUES ('0010',1234,1117,2) ;
INSERT INTO TEST(USER_ID, KJ_CODE,PG_CODE,STATUS_CODE ) VALUES ('0010',4321,1111,1) ;
INSERT INTO TEST(USER_ID, KJ_CODE,PG_CODE,STATUS_CODE ) VALUES ('0010',4321,1112,1) ;
INSERT INTO TEST(USER_ID, KJ_CODE,PG_CODE,STATUS_CODE ) VALUES ('0010',4321,1113,2) ;
INSERT INTO TEST(USER_ID, KJ_CODE,PG_CODE,STATUS_CODE ) VALUES ('0010',4321,1114,2) ;
INSERT INTO TEST(USER_ID, KJ_CODE,PG_CODE,STATUS_CODE ) VALUES ('0010',4321,1115,3) ;

そして、KJコードをGROUP BYで集約しつつPG_CODEをCOUNTした合計数と、
ステータスコード1～4のそれぞれのCOUNTした数をだし、
ステータスコード1～4それぞれのCOUNTした数とPG_CODEをCOUNTした数、
あるいはステータスコードの合計を例えば下記のように比較します。

ステータスコードの値１の合計数 > PG_CODEの合計数
ステータスコードの値１の合計数 < ステータスコードの値２の合計数
ステータスコードの値３の合計数 > 0
ステータスコードの値４の合計数 < PG_CODEの合計数

そのうえで最終的に、
ユーザーID、GROUP BYしたKJコード、CASEか何かで比較したステータスコード
のデータを取得したいです。

KJ_CODEごとにSTATUS_CODE の一番数が多いもの、あるいは１個以上ある場合など
例えば、サンプルデータでKJ_CODEが1234のとき、
status1の数が KJ_CODE（1234)のPG_CODE数より大きいとstatus1を表示
status2の数が１個以上のときはstatus2を表示、ということを想定しています。

発生している問題

初歩的で申し訳ありませんが、一つのテーブルでGROUP BYしつつ、
上記のように二つの列をカウントするような記述がよくわかっていません。
こういった場合は、やはり同一テーブルを副問い合わせなどするのでしょうか。
組み方、考え方をご教授頂けますと幸いです。

現在私自身は、下記のようなＳＱＬの記述状況で手間取ってしまっています。

SELECT
,USER_ID
,KJ_CODE
,COUNT(PG_CODE)
,COUNT(STATUS_CODE)
FROM
TEST
GROUP BY
KJ_CODE

Comments

[Orlofsky]

GROUP BY
の空白は勝手に省略するとエラーになりますから、省略しないでください。
スペルは見直してね。
質問は修正できます。

[Orlofsky]

同じSQLでもデータベースやそのバージョンによって方言が大きいですから、どのデータベースを使うのかを質問のタグで示したり、質問にバージョンも明記した方が適切なコメントが付き易いです。

[Orlofsky]

ここの掲示板ではMarkdownの使い方を覚えてください。
テーブル定義はCREATE TABLE文で、テーブル中のデータはINSERT文で https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正できるとSQLの動作確認が容易になるので、適切なコメントが付き易くなります。

[m.ts10806]

細かくて申し訳ないですが、冒頭の挨拶はなくていいです。
淡々と要件のみ記載してください。

[sousuke]

「KJコードをCOUNTした数」っていうのはサンプルデータだといくつになるのか、その理由を伝えたほうがいいと思います。

[sazi]

集計の単位がいまいちよく分かりません。
サンプルデータを元に、どういう結果を取得されたいのかを追記された方が良いと思います。

[退会済みユーザー]

「Oracle Database」ってタグがあるので付けてください。
質問がより詳しい方の目に留まるようになります。（回答がもらえること、解決することは別）

[UmiSea2]

ご指摘のほどありがとうございました。

[Orlofsky]

集計し易さを考えたコード設計にしようって考えた方が良いかも？

Answer 1

SELECT  USER_ID, KJ_CODE, COUNT(*) kj_count
      , sum(case when STATUS_CODE=1 then 1 else 0 end) status1_count
      , sum(case when STATUS_CODE=2 then 1 else 0 end) status2_count
      , sum(case when STATUS_CODE=3 then 1 else 0 end) status3_count
      , sum(case when STATUS_CODE=4 then 1 else 0 end) status4_count
FROM  TEST
GROUP BY  USER_ID, KJ_CODE

加工するなら

select USER_ID, KJ_CODE
     , case  when  status1_count > kj_count then 'XXX' end
from (
    SELECT  USER_ID, KJ_CODE, COUNT(*) kj_count
          , sum(case when STATUS_CODE=1 then 1 else 0 end) status1_count
          , sum(case when STATUS_CODE=2 then 1 else 0 end) status2_count
          , sum(case when STATUS_CODE=3 then 1 else 0 end) status3_count
          , sum(case when STATUS_CODE=4 then 1 else 0 end) status4_count
    FROM  TEST
    GROUP BY  USER_ID, KJ_CODE
)

Comments

[UmiSea2]

ご回答ありがとうございます。サンプルテーブルの項目が一つ足りなかったため、さきほど修正いたましたが、項目を変更して確認させていただきます。

[UmiSea2]

ありがとうございました。無事想定されている動作を組むことができました。

Answer 2

そのうえで最終的に、ユーザーID、GROUP BYしたKJコード、CASEか何かで比較したステータスコードのデータを取得したいです。

user_idとkj_codeごとに、ステータス1~4がそれぞれいくつあるかを数えたいということであっていますか?

であれば、以下のようなSQLで実現できるかと思います。

SELECT
  user_id,
  kj_code,
  COUNT(status_code=1 OR NULL) AS status1,
  COUNT(status_code=2 OR NULL) AS status2,
  COUNT(status_code=3 OR NULL) AS status3,
  COUNT(status_code=4 OR NULL) AS status4
FROM TEST
GROUP BY user_id, kj_code

この実行結果は以下のようになります。

user_id	kj_code	status1	status2	status3	status4
0010	4321	2	2	1	0
0010	1234	2	2	2	1

Comments

[UmiSea2]

ご回答ありがとうございます。わかりにくい質問ですいません。
想定は、KJ_CODEが仮に二種類あると二件のデータが取得される想定です。
KJ_CODEごとにSTATUS_CODE の一番数が多いもの、あるいは１個以上ある場合など
例えば、サンプルデータでKJ_CODEが1234のとき、
status1の数が KJ_CODE（1234)の数より大きいとstatus1を表示
status2の数が１個以上のときはstatus2を表示、ということを想定している状態です。

[sazi]

@UmiSea2さん
この回答のSQLで条件判断するための情報は取得できると思うのですが？
Caseでの判断などは、上記をインラインビューにしてネストさせて行えば良いのでは？
@neko_the_shadowさん
よく見ると、真偽をカウントしているので、駄目な気がします。
その前に、oracleではエラーですが。

Answer 3

テータベースによっては、GROUP BY と共に、ROLLUP, CUBE 辺りを使うといい場合ものあります。

Comments

[UmiSea2]

ありがとうございます。確認させていただきます。