🎄teratailクリスマスプレゼントキャンペーン2024🎄』開催中!

\teratail特別グッズやAmazonギフトカード最大2,000円分が当たる!/

詳細はこちら
GROUP BY

GROUP BYとはSQL文のひとつで、SELECT文において特定の列の値が等しい行ごとに表をグループ化します。

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

4回答

5273閲覧

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

Vitamin_Lemon

総合スコア4

GROUP BY

GROUP BYとはSQL文のひとつで、SELECT文において特定の列の値が等しい行ごとに表をグループ化します。

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

1クリップ

投稿2019/10/31 01:29

編集2019/10/31 03:00

前提・実現したいこと

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

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

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

該当のソースコード

SQL

1WITH CTE as ( 2SELECT 3DE.[MNTH] AS [年月] 4,DE.TKIB AS [得意先コード] 5,OP.KTKM AS [NAME] 6,TK.[K集計] AS [契約集計コード] 7,TK.[S集計] AS [施設コード] 8,ISNULL(SUM(DE.[KIN]), 0) AS [現行価(多店舗)] 9,ISNULL(SUM(DE.[KIN]), 0) AS [現行価(契約施設)] 10,ISNULL(SUM(DE.[AKIN]), 0) AS [契約価(多店舗)] 11,ISNULL(SUM(DE.[AKIN]), 0) AS [契約価(契約施設)] 12,ISNULL(SUM(DE.[ASA]), 0) AS [差額(多店舗)] 13,ISNULL(SUM(DE.[ASA]), 0) AS [差額(契約施設)] 14FROM vkadmin.[出力マスタ] OP WITH (NOLOCK) 15INNER JOIN vkadmin.[商品詳細] DE WITH(NOLOCK) 16ON OP.[得意先コード] = DE.[TKIB] 17INNER JOIN vkadmin.[得意先マスタ] TK WITH(NOLOCK) 18ON OP.[得意先コード] = TK.[得意先コード] 19WHERE DE.[MNTH] BETWEEN '201904' AND '201909' 20 AND DE.[TKIB] = (SELECT [K集計] 21 FROM vkadmin.得意先マスタ WITH(NOLOCK) 22 WHERE [得意先コード] = '0732771') 23 or DE.[TKIB] = (SELECT [S集計] 24 FROM vkadmin.得意先マスタ WITH(NOLOCK) 25 WHERE [得意先コード] = '0732771') 26GROUP BY DE.[MNTH],OP.[KRAN],OP.[IRAN],DE.[TKIB],TK.[K集計],TK.[S集計],OP.[KTKM] 27) 28 29SELECT [年月] 30,ISNULL(CASE WHEN (SELECT [K集計] 31FROM vkadmin.得意先マスタ WITH(NOLOCK) 32WHERE [得意先コード] = '0732771') = [得意先コード] THEN SUM([現行価(多店舗)] ) ELSE 0 END,0) AS [現行価(多店舗)] 33,ISNULL(CASE WHEN (SELECT [S集計] 34FROM vkadmin.得意先マスタ WITH(NOLOCK) 35WHERE [得意先コード] = '0732771') = [得意先コード] THEN SUM([現行価(契約施設)]) ELSE 0 END,0) AS [現行価(契約施設)] 36,ISNULL(CASE WHEN (SELECT [K集計] 37FROM vkadmin.得意先マスタ WITH(NOLOCK) 38WHERE [得意先コード] = '0732771') = [得意先コード] THEN SUM([契約価(多店舗)]) ELSE 0 END,0) AS [契約価(多店舗)] 39,ISNULL(CASE WHEN (SELECT [S集計] 40FROM vkadmin.得意先マスタ WITH(NOLOCK) 41WHERE [得意先コード] = '0732771') = [得意先コード] THEN SUM([契約価(契約施設)]) ELSE 0 END,0) AS [契約価(契約施設)] 42,ISNULL(CASE WHEN (SELECT [K集計] 43FROM vkadmin.得意先マスタ WITH(NOLOCK) 44WHERE [得意先コード] = '0732771') = [得意先コード] THEN SUM([差額(多店舗)]) ELSE 0 END,0) AS [差額(多店舗)] 45,ISNULL(CASE WHEN (SELECT [S集計] 46FROM vkadmin.得意先マスタ WITH(NOLOCK) 47WHERE [得意先コード] = '0732771') = [得意先コード] THEN SUM([差額(契約施設)]) ELSE 0 END,0) AS [差額(契約施設)] 48FROM CTE 49 50GROUP BY [年月],[得意先コード] 51ORDER BY [年月] 52'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

年月ごとに得意先コードが異なるため同じ年月に2つの数値が出てきてしまいます。
これを年月ごと、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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Y.H.

2019/10/31 04:05

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

回答4

0

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

SQL

1WITH CTE1 as ( 2SELECT 3DE.[MNTH] AS [年月] 4,DE.TKIB AS [得意先コード] 5,OP.KTKM AS [NAME] 6,ISNULL(SUM(DE.[KIN]), 0) AS [現行価(多店舗)] 7,ISNULL(SUM(DE.[AKIN]), 0) AS [契約価(多店舗)] 8,ISNULL(SUM(DE.[ASA]), 0) AS [差額(多店舗)] 9FROM vkadmin.[出力マスタ] OP WITH (NOLOCK) 10INNER JOIN vkadmin.[商品詳細] DE WITH(NOLOCK) 11ON OP.[得意先コード] = DE.[TKIB] 12INNER JOIN vkadmin.[得意先マスタ] TK WITH(NOLOCK) 13ON OP.[得意先コード] = TK.[得意先コード] 14WHERE DE.[MNTH] BETWEEN '201904' AND '201909' 15 AND DE.[TKIB] = (SELECT [K集計] 16 FROM vkadmin.得意先マスタ WITH(NOLOCK) 17 WHERE [得意先コード] = '0732771') 18GROUP BY DE.[MNTH],OP.[KRAN],OP.[IRAN],DE.[TKIB],TK.[K集計],TK.[S集計],OP.[KTKM] 19) 20, 21WITH CTE2 as ( 22SELECT 23DE.[MNTH] AS [年月] 24,DE.TKIB AS [得意先コード] 25,OP.KTKM AS [NAME] 26,ISNULL(SUM(DE.[KIN]), 0) AS [現行価(契約施設)] 27,ISNULL(SUM(DE.[AKIN]), 0) AS [契約価(契約施設)] 28,ISNULL(SUM(DE.[ASA]), 0) AS [差額(契約施設)] 29FROM vkadmin.[出力マスタ] OP WITH (NOLOCK) 30INNER JOIN vkadmin.[商品詳細] DE WITH(NOLOCK) 31ON OP.[得意先コード] = DE.[TKIB] 32INNER JOIN vkadmin.[得意先マスタ] TK WITH(NOLOCK) 33ON OP.[得意先コード] = TK.[得意先コード] 34WHERE DE.[MNTH] BETWEEN '201904' AND '201909' 35 AND 36 DE.[TKIB] = (SELECT [S集計] 37 FROM vkadmin.得意先マスタ WITH(NOLOCK) 38 WHERE [得意先コード] = '0732771') 39GROUP BY DE.[MNTH],OP.[KRAN],OP.[IRAN],DE.[TKIB],OP.[KTKM] 40) 41 42SELECT CTE1.[年月] 43,ISNULL(SUM(CTE1.[現行価(多店舗)]),0) AS [現行価(多店舗)] 44,ISNULL(SUM(CTE2.[現行価(契約施設)]),0) AS [現行価(契約施設)] 45,ISNULL(SUM(CTE1.[契約価(多店舗)]),0) AS [契約価(多店舗)] 46,ISNULL(SUM(CTE2.[契約価(契約施設)]),0) AS [契約価(契約施設)] 47,ISNULL(SUM(CTE1.[差額(多店舗)]),0) AS [差額(多店舗)] 48,ISNULL(SUM([差額(契約施設)]),0) AS [差額(契約施設)] 49FROM CTE1 50LEFT JOIN CTE2 51ON CTE1.[年月] = CTE2.[年月] 52GROUP BY [年月],[得意先コード] 53ORDER BY [年月]

投稿2019/11/01 03:32

shirokuma4690

総合スコア154

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

あらかじめ

SQL

1SET NOCOUNT ON; 2DECLARE @KS VARCHAR(20) = ( 3 SELECT 4 [K集計] 5 FROM 6 VKADMIN.得意先マスタ WITH(NOLOCK) 7 WHERE 8 [得意先コード] = '0732771' 9 ); 10DECLARE @SS VARCHAR(20) = ( 11 SELECT 12 [S集計] 13 FROM 14 VKADMIN.得意先マスタ WITH(NOLOCK) 15 WHERE 16 [得意先コード] = '0732771' 17 );

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

WITH で

SQL

1WITH CTE AS ( 23), CTE1 AS ( 4 SELECT 5 [K集計] 6 , [S集計] 7 FROM 8 VKADMIN.得意先マスタ WITH(NOLOCK) 9 WHERE 10 [得意先コード] = '0732771' 11)

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

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

例)

SQL

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

SQL

1ISNULL( 2 SUM( 3 CASE 4 WHEN @KSS = [得意先コード] 5 THEN [現行価(多店舗) ] 6 ELSE 0 7 END 8 ), 0 9) AS [現行価(多店舗)]

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

投稿2019/10/31 08:46

KOZ6.0

総合スコア2707

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

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

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

投稿2019/10/31 03:35

hatena19

総合スコア34073

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

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

得意先コード年月集計値
hoge2019/10/31100
hoge2019/10/31150
fuga2019/10/31200

投稿2019/10/31 02:07

ryo_y

総合スコア244

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Vitamin_Lemon

2019/10/31 03:04

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.36%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問