SQLで指定の要素をGroup Byから外したい

Question

前提・実現したいこと

SQLで抽出したデータを画面に表示させるコードを作成しています。
年月ごとに、データを集計したいです。
なので、GROUP BY から[得意先コード]の要素を外したいのですが
外すとエラーになってしまいます。

発生している問題・エラーメッセージ

メッセージ 8120、レベル 16、状態 1、行 37
列 'CTE.[得意先コード]' は選択リスト内では無効です。この列は集計関数または GROUP BY 句に含まれていません。

該当のソースコード

WITH CTE as (
SELECT 
DE.[MNTH] AS [年月] 
,DE.TKIB AS [得意先コード]
,OP.KTKM AS [NAME]
,TK.[K集計] AS [契約集計コード]
,TK.[S集計] AS [施設コード]
,ISNULL(SUM(DE.[KIN]), 0)  AS [現行価(多店舗)]
,ISNULL(SUM(DE.[KIN]), 0)  AS [現行価(契約施設)]
,ISNULL(SUM(DE.[AKIN]), 0) AS [契約価(多店舗)]
,ISNULL(SUM(DE.[AKIN]), 0) AS [契約価(契約施設)]
,ISNULL(SUM(DE.[ASA]), 0)  AS [差額(多店舗)]
,ISNULL(SUM(DE.[ASA]), 0)  AS [差額(契約施設)]
FROM vkadmin.[出力マスタ] OP WITH (NOLOCK)
INNER JOIN vkadmin.[商品詳細] DE WITH(NOLOCK)
ON  OP.[得意先コード] = DE.[TKIB]
INNER JOIN vkadmin.[得意先マスタ] TK WITH(NOLOCK)
ON  OP.[得意先コード] = TK.[得意先コード]
WHERE DE.[MNTH] BETWEEN '201904' AND '201909'
  AND DE.[TKIB] = (SELECT [K集計] 
			FROM vkadmin.得意先マスタ WITH(NOLOCK)
			WHERE [得意先コード] = '0732771')
 or DE.[TKIB] = (SELECT [S集計] 
			FROM vkadmin.得意先マスタ WITH(NOLOCK)
			WHERE [得意先コード] = '0732771')
GROUP BY DE.[MNTH],OP.[KRAN],OP.[IRAN],DE.[TKIB],TK.[K集計],TK.[S集計],OP.[KTKM]
)

SELECT [年月]
,ISNULL(CASE WHEN (SELECT [K集計] 
FROM vkadmin.得意先マスタ WITH(NOLOCK)
WHERE [得意先コード] = '0732771') = [得意先コード] THEN SUM([現行価(多店舗)] ) ELSE 0 END,0) AS [現行価(多店舗)] 
,ISNULL(CASE WHEN (SELECT [S集計] 
FROM vkadmin.得意先マスタ WITH(NOLOCK)
WHERE [得意先コード] = '0732771') = [得意先コード] THEN SUM([現行価(契約施設)]) ELSE 0 END,0) AS [現行価(契約施設)] 
,ISNULL(CASE WHEN (SELECT [K集計] 
FROM vkadmin.得意先マスタ WITH(NOLOCK)
WHERE [得意先コード] = '0732771') = [得意先コード] THEN SUM([契約価(多店舗)]) ELSE 0 END,0) AS [契約価(多店舗)] 
,ISNULL(CASE WHEN (SELECT [S集計] 
FROM vkadmin.得意先マスタ WITH(NOLOCK)
WHERE [得意先コード] = '0732771') = [得意先コード] THEN SUM([契約価(契約施設)]) ELSE 0 END,0) AS [契約価(契約施設)] 
,ISNULL(CASE WHEN (SELECT [K集計] 
FROM vkadmin.得意先マスタ WITH(NOLOCK)
WHERE [得意先コード] = '0732771') = [得意先コード] THEN SUM([差額(多店舗)]) ELSE 0 END,0) AS [差額(多店舗)]
,ISNULL(CASE WHEN (SELECT [S集計] 
FROM vkadmin.得意先マスタ WITH(NOLOCK)
WHERE [得意先コード] = '0732771') = [得意先コード] THEN SUM([差額(契約施設)]) ELSE 0 END,0) AS [差額(契約施設)]
FROM CTE

GROUP BY [年月],[得意先コード]
ORDER BY [年月]
※'0732771'は、都度入力するパラメータです。

試したこと

S集計,K集計とぶつける得意先コードは、共通式の上部分のクエリで取得しますが
共通式での[得意先コード] と同じ値のままで抽出してくれる他の方法が見当たらず困っています。

補足情報（FW/ツールのバージョンなど）

補足）10/31 11:31
現在のコードで取得できるのはこのような形です。(見づらくてすみません)

[年月][現行価(多店舗)][現行価(契約施設)][契約価(多店舗)][契約価(契約施設)][差額(多店舗)][差額(契約施設)]
201904 5407801 0000000 5223491 0000000 -184310 0000000
201904 0000000 2127342 0000000 2090846 0000000 -36496
201905 0000000 2090724 0000000 2044902 0000000 -45822
201905 4761532 0000000 4597416 0000000 -164116 0000000
201906 0189238 0000000 3069219 0000000 -120019 0000000
201906 0000000 1725209 0000000 1695254 0000000 -29955
201907 0000000 2412356 0000000 2348237 0000000 -64119
201907 5869159 0000000 5676023 0000000 -193136 0000000
201908 0000000 1962943 0000000 1925071 0000000 -37872
201908 5172795 0000000 4983429 0000000 -189366 0000000
201909 4061538 0000000 3963035 0000000 -98503 0000000
201909 0000000 2129935 0000000 2094667 0000000 -35268

年月ごとに得意先コードが異なるため同じ年月に２つの数値が出てきてしまいます。
これを年月ごと、1行のみに集計したいです。
そのため、GROUP BYから得意先コードを外したいです。
例）
201904 5407801 0000000 5223491 0000000 -184310 0000000
201904 0000000 2127342 0000000 2090846 0000000 -36496
↓
201904 5407801 2127342 5223491 2090846 -184310 -36496

環境
Windows10
使用ツール
・SQL Server Manegement Studio

Comments

[Y.H.]

例で記載されている、得意先コード「5407801」を抽出する規則を質問に記載ください。
9999999、5407801、1111111、0000000 の4つあった場合はどれを結果として取得したいですか？

Answer 1

CTEの部分を他店舗と契約施設に分けてみてはどうでしょうか

WITH CTE1 as (
SELECT 
DE.[MNTH] AS [年月] 
,DE.TKIB AS [得意先コード]
,OP.KTKM AS [NAME]
,ISNULL(SUM(DE.[KIN]), 0)  AS [現行価(多店舗)]
,ISNULL(SUM(DE.[AKIN]), 0) AS [契約価(多店舗)]
,ISNULL(SUM(DE.[ASA]), 0)  AS [差額(多店舗)]
FROM vkadmin.[出力マスタ] OP WITH (NOLOCK)
INNER JOIN vkadmin.[商品詳細] DE WITH(NOLOCK)
ON  OP.[得意先コード] = DE.[TKIB]
INNER JOIN vkadmin.[得意先マスタ] TK WITH(NOLOCK)
ON  OP.[得意先コード] = TK.[得意先コード]
WHERE DE.[MNTH] BETWEEN '201904' AND '201909'
  AND DE.[TKIB] = (SELECT [K集計] 
            FROM vkadmin.得意先マスタ WITH(NOLOCK)
            WHERE [得意先コード] = '0732771')
GROUP BY DE.[MNTH],OP.[KRAN],OP.[IRAN],DE.[TKIB],TK.[K集計],TK.[S集計],OP.[KTKM]
)
,
WITH CTE2 as (
SELECT 
DE.[MNTH] AS [年月] 
,DE.TKIB AS [得意先コード]
,OP.KTKM AS [NAME]
,ISNULL(SUM(DE.[KIN]), 0)  AS [現行価(契約施設)]
,ISNULL(SUM(DE.[AKIN]), 0) AS [契約価(契約施設)]
,ISNULL(SUM(DE.[ASA]), 0)  AS [差額(契約施設)]
FROM vkadmin.[出力マスタ] OP WITH (NOLOCK)
INNER JOIN vkadmin.[商品詳細] DE WITH(NOLOCK)
ON  OP.[得意先コード] = DE.[TKIB]
INNER JOIN vkadmin.[得意先マスタ] TK WITH(NOLOCK)
ON  OP.[得意先コード] = TK.[得意先コード]
WHERE DE.[MNTH] BETWEEN '201904' AND '201909'
  AND 
 DE.[TKIB] = (SELECT [S集計] 
            FROM vkadmin.得意先マスタ WITH(NOLOCK)
            WHERE [得意先コード] = '0732771')
GROUP BY DE.[MNTH],OP.[KRAN],OP.[IRAN],DE.[TKIB],OP.[KTKM]
)

SELECT CTE1.[年月]
,ISNULL(SUM(CTE1.[現行価(多店舗)]),0) AS [現行価(多店舗)] 
,ISNULL(SUM(CTE2.[現行価(契約施設)]),0) AS [現行価(契約施設)] 
,ISNULL(SUM(CTE1.[契約価(多店舗)]),0) AS [契約価(多店舗)] 
,ISNULL(SUM(CTE2.[契約価(契約施設)]),0) AS [契約価(契約施設)] 
,ISNULL(SUM(CTE1.[差額(多店舗)]),0) AS [差額(多店舗)]
,ISNULL(SUM([差額(契約施設)]),0) AS [差額(契約施設)]
FROM CTE1
LEFT JOIN CTE2
ON CTE1.[年月]  = CTE2.[年月]
GROUP BY [年月],[得意先コード]
ORDER BY [年月]

Answer 2

あらかじめ

SET NOCOUNT ON;
DECLARE @KS VARCHAR(20) = (
        SELECT
            [K集計]
        FROM
            VKADMIN.得意先マスタ    WITH(NOLOCK)
        WHERE
            [得意先コード]  =   '0732771'
    );
DECLARE @SS VARCHAR(20) = (
        SELECT
            [S集計]
        FROM
            VKADMIN.得意先マスタ    WITH(NOLOCK)
        WHERE
            [得意先コード]  =   '0732771'
    );

としたあとに @KS と @SS を使って SQL を書くとスッキリすると思います。

WITH で

WITH CTE AS (
    略
), CTE1 AS (
        SELECT
            [K集計]
        ,   [S集計]
        FROM
            VKADMIN.得意先マスタ    WITH(NOLOCK)
        WHERE
            [得意先コード]  =   '0732771'
)

として CROSS JOIN してもいいかも。

得意先コードを GROUP BY から外せないのは、SUM をつける場所がマズいのだと思います。
CASE の中で SUM するのではなく、CASE の外で SUM すれば良いのでは？

例)

ISNULL(
        CASE
            WHEN @KSS = [得意先コード]
            THEN SUM([現行価(多店舗) ])
            ELSE 0
        END ,   0
    )   AS [現行価(多店舗)]

を

ISNULL(
	SUM(
            CASE
                WHEN @KSS = [得意先コード]
                THEN [現行価(多店舗) ]
                ELSE 0
            END		
	   ), 0
) AS [現行価(多店舗)]

に変えると通らないでしょうか。

Answer 3

GROUP BYで得意先コードは指定せずに、SELECT で得意先コードを表示させたい、ということでしょうか。

すでに回答が出ていますか、GROUP BY 以外のフィールドには集計関数が必要になります。
今回の場合なら、MAX関数かMIN関数で囲めばどうでしょう。

Answer 4

GROUP BYで集計した際にカラムの値を取得する場合は、取得する列もGROUP BYに含めるか、集計関数で集計する必要があります。
例えば、下記のようなデータが入っていたとする場合、年月だけでグループ化してしまうと、取得する得意先コードを特定できなくなってしまいます。

得意先コード	年月	集計値
hoge	2019/10/31	100
hoge	2019/10/31	150
fuga	2019/10/31	200

Comments

[Vitamin_Lemon]

回答ありがとうございます。
言葉で説明が難しいので、補足にイメージを追記しました。
現在は同じ月に２つの値が取得されています。
SELECTで得意先コードを使用しているので、
GROUP BYでも得意先コードを指定してしまってます。
これを月ごとのみで集計できる方法があればありがたいです。
申し訳ございません、よろしくお願いいたします。