オラクル SQLのPARTITION BYからHAVING

オラクル SQLのPARTITION BYの結果をさらにHAVING条件のように絞りたいです。
実現するにはどうすればいいでしょうか。

例えば、下のテーブルから以下を取りたいです。

テーブル

|品番|カラー|サイズ|在庫|
|:--|:--:|--:|
|A10000|青|S|35|
|A10000|青|M|0|
|A10000|青|L|5|
|A10000|赤|S|61|
|A10000|ホワイト|S|21|
|A10000|ブラック|L|0|

欲しい項目

太字の取り方が分かりません。
・品番
・カラー
・在庫合計(品番単位)
・在庫合計(カラー単位)
・在庫0の件数(品番単位)
・在庫0の件数(カラー単位)

欲しい結果

|品番|カラー|在庫合計(品番単位)|在庫合計(カラー単位)|在庫0の件数(品番単位)|在庫0の件数(カラー単位)|
|:--|:--:|--:|||
|A10000|青|122|40|2|1|
|A10000|赤|122|61|2|0|
|A10000|ホワイト|122|21|2|0|
|A10000|ブラック|122|0|2|1|

書いているSQLはこんな感じです

SQL
1SELECT DISTINCT
2 品番
3,カラー
4,SUM(在庫) OVER(PARTITION BY 品番)		AS '在庫合計(品番単位)'
5,SUM(在庫) OVER(PARTITION BY 品番,カラー)  AS '在庫合計(カラー単位)'
6,?????                                    AS '在庫0の件数(品番単位)'
7,?????                                    AS '在庫0の件数(カラー単位)'
8
9FROM テーブル

HAVINGがGROUP BYの時のように使えれば良いのですが...
COUNT(PLAN_AMOUNT) OVER(PARTITION BY ITEM_ID HAVING SUM(STOCK_AMOUNT) < 1)

Orlofsky

2020/05/24 11:48

HAVING やそれに類する機能でどんな結果が欲しいのかわかりません。質問のテーブル定義とデータは CREATE TABLE文とINSERT文で、希望されるSELECT結果も含めて https://teratail.com/help/question-tips#questionTips3-7 の [コード] で提示できると動作確認し易いので、適切なコメントが付き易いです。

行動規範の内容に同意します

回答1件

ベストアンサー

こういう場面ではcase式が猛威を振るいます。

sql
1SELECT DISTINCT
2	品番
3	,カラー
4	,sum(在庫) over(partition by 品番) as 在庫合計品番単位
5	,sum(在庫) over(partition by カラー) as 在庫合計カラー単位
6	,sum(case 在庫 when 0 then 1 else 0 end) over(partition by 品番) as 在庫0の件数品番単位
7	,sum(case 在庫 when 0 then 1 else 0 end) over(partition by カラー) as 在庫0の件数品番単位
8FROM tbl