SQL グループ化する時の条件に判断を追加したい

Question

SQLServerのビューを利用して集計用のビューを作成しています。
WHERE句で一部の条件を外した所までは行けたのですが、IF文のような判断を追加したいんですが、書き方がわかりません。(出来るのかすら分かりませんが…)

以下のようなテーブルから、[コード]でグループ化、条件で[削除]をFalseのみ、までは行けました。

|コード|製品名|受注数|再検品|枝番|削除|
|:--|:--:|--:|--:|
|123|エビ|300|0|1|False|
|456|カニ|300|0|1|False|
|789|ウニ|300|0|1|False|
|123|エビ|400|0|2|False|
|123|エビ|450|1|2|False|
|456|カニ|600|0|2|True|
|789|ウニ|700|0|2|False|

SELECT MAX(コード) AS コード, MAX(製品名) AS 製品名, SUM(受注数) AS 受注数
FROM 受注テーブル
WHERE 削除 = 'False'
GROUP BY コード

コード	製品名	受注数
123	エビ	1150
456	カニ	300
789	ウニ	1000

しかしこの文だと、再検品を追加すると前の分も加算されてしまうので、
グループ内の[コード]と[枝番]が同じで[再検品]に「0」と「1」があった場合、「0」の行は除き「1」の行を集計に入れるという事がしたいです。
何か良い方法はありますでしょうか？
理想形↓

コード	製品名	受注数
123	エビ	750
456	カニ	300
789	ウニ	1000

###環境
Windows Server 2012R2
SQL Server 2012

Comments

[sazi]

同じ[コード]と[枝番]では[再検品]は1件だけですか？

Answer 1

入れ子だとごちゃごちゃするので仮想テーブルを使って書きました。
どうでしょうか？

-- まず集計対象となるデータの仮想テーブルを作る
WITH target AS (
    SELECT 
    　　コード
    　, 枝番
      , MAX(再検品) AS MAX再検品
    FROM 受注テーブル
    WHERE 削除 = 'False'
    GROUP BY 
    　コード
     ,枝番
)

-- 仮想テーブルと受注テーブルをJOINしたものをGROUP化してSUMする
SELECT 
　J.コード
 ,J.製品名
 ,SUM(J.受注数)
FROM 受注テーブル AS J INNER JOIN target AS T 
 ON J.コード = T.コード 
AND J.枝番   = T.枝番
AND J.再検品 = T.MAX再検品
GROUP BY
　J.コード
 ,J.製品名

Answer 2

コードと枝番が同じ場合は、再検品の値がより大きい行を集計に入れたいということであっていますか?

あっているのであれば、以下のようなクエリになるかと思います。

SELECT MAX(コード) AS コード, MAX(製品名) AS 製品名, SUM(受注数) AS 受注数
FROM 受注テーブル G1
WHERE 削除 = 'False'
AND NOT EXISTS (
  SELECT *
  FROM 受注テーブル G2
  WHERE G2.削除 = 'False'
  AND G1.コード = G2.コード
  AND G1.枝番 = G2.枝番
  AND G1.再検品 < G2.再検品
)
GROUP BY コード

あるいはWindow関数を使う方法もあります、

SELECT MAX(コード) AS コード, MAX(製品名) AS 製品名, SUM(受注数) AS 受注数
FROM (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY コード, 枝番 ORDER BY 再検品 DESC) AS ランク
  FROM 受注テーブル
  WHERE 削除 = 'False'
) T
WHERE ランク = 1
GROUP BY コード

Comments

[nuko3]

ありがとうございます！理想的な形になれました！

Answer 3

グループ内の[コード]と[枝番]が同じで[再検品]に「0」と「1」があった場合、「0」の行は除き「1」の行を集計に入れるという事がしたいです。

やり方は色々あるだろうけども、直感的なわかりやすさなら

1. 「再検品=1」の行だけ抽出
2. 1.の表と元の票を「コード」「枝番」「再検品=0」でJOIN
3. CASE (Transact-SQL)文で1.の表がNULLでない場合は1.の表から、それ以外は元の表から集計用のデータを抽出する
4. 3.できた表を質問文の条件で集計

Comments

[nuko3]

回答ありがとうございます。
実際にやってみて1.と2.までは行けて再検品がある0のものを抽出できる所までは出来ました。
3.の原理はなんとなく理解出来るのですが、どう書けばいいのかわかりません…

[gentaro]

書き方はリファレンスに載ってますし、ググればいくらでも事例が見つかるでしょう。
「わかりません」と言って解決するものじゃありません。
わからないなら何をどこまで調べてどの部分が読み解けなかったのか説明しましょう。

[nuko3]

すみません、文章を端折りすぎました。
条件の書き方が分からず、SELECT CASE ID IS NULL ...と書いてもエラー(','付近に不適切な構文があります)と出るので、根本的な事か3.の文章の読み解き方が間違っているんだろうなと思ってリファレンス見たり色々調べながらやっていましたがうまく行かず悩んでいました。