• ベストアンサー
  • すぐに回答を！

集計のSQLについて

皆さんの知恵をお借りしたいと思っています。 どなたか、良い案がある方お願いいたします。 以下のテーブルがあります。 （売上テーブル） 品名　　　金額　　　購入者 ---------------------------- 鉛筆　　　20　　　　Ａさん 鉛筆　　　40　　　　Ｂさん 鉛筆　　　30　　　　Ｂさん 鉛筆　　　30　　　　　　　 鉛筆　　　10　　　　　　　 上のテーブルで、品名・購入者を集計キーとして、 金額を集計したい場合、 SELECT 品名,購入者,SUM(金額) FROM 売上テーブル GROUP BY 品名,購入者 　というSQL文で, 　 品名　　　金額　　　購入者 ---------------------------- 鉛筆　　　20　　　　Ａさん 鉛筆　　　70　　　　Ｂさん 鉛筆　　　40　　　　　　　 という結果が得られると思いますが・・・、 これを、 品名　　　金額　　　購入者 ---------------------------- 鉛筆　　　20　　　　Ａさん 鉛筆　　　70　　　　Ｂさん 鉛筆　　　30　　　　　　　 鉛筆　　　10　　　　　　　 という結果になるようなSQL文を作りたいのです。 購入者が入っているものについては、品名・購入者で集計して、 購入者が空欄のものは集計しないようにしたいと思っています。 UNIONを使えばできるとは思うのですが、 それ以外の方法で、1回のSQL文でできる方法があれば知りたいと思っています。 よろしくお願いします。

  1. カテゴリ
  2. [技術者向] コンピューター
  3. データベース
  4. Oracle

共感・応援の気持ちを伝えよう！

  • 回答数6
  • 閲覧数247
  • ありがとう数15

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.5
  • jmh
  • ベストアンサー率23% (71/304)

こんばんは。 group by 品名, 購入者, nvl(購入者, rowidtochar(rowid)) は、どうでしょうか？

共感・感謝の気持ちを伝えよう！

質問者からのお礼

回答ありがとうございます。 NVLを使うという案、使わせていただきます。 今回のテーブルには、ちょうど主キーがあるので、 NO１の方の、主キーを使うというアイディアと合わせて、 group by 品名, 購入者, nvl(購入者, 主キー) という感じにしました。 ただ、扱っているテーブルの中に、 主キーが存在しないテーブルが、少しあるので、 もしかしたら、 nvl(購入者, rowidtochar(rowid)) の部分を、そのまま使わせていただくことがあるかもしれません。 NVLはいつも使っている関数なのですが、 今回のSQL文で、このように使うということを思いつきませんでした。 今回とても助かりました。 ありがとうございました。

関連するQ&A

  • SQL 集計について

    集計方法がどうしてもわからないのでご教授ください。 下記のテーブルを持っています。 テーブル1では日々の売上金額を管理し、 テーブル2では一週間の日付が保存されています。（月曜日～日曜日） -------テーブル１-------　　　　　 　 　　　日付　　　　売上金額 　 2013/09/01　　　　　3000　　 2013/09/02　　　　　4000　　 2013/09/03　　　　　2000　　 　・　　　　　　　　　　　　 　・　　　　　　　　　 　・　　　　　　　　　　 2013/09/30　　　　　1000 ----------------------- --------テーブル2--------- 　日付(月)　　　　日付(日)　　　 2013/08/26　　　2013/09/01 2013/09/02　　　2013/09/08 　　　・　　　　 　　　・ 　　　・　　　　　　 2013/09/30　　　2013/10/06 ------------------------- テーブル1とテーブル2の情報をもとに テーブル3にある、日付（月）と日付（日）は、テーブル2から持ってきて、 一週間の集計を行いたいと思っております。 --------------テーブル3-------------- 　日付(月)　　　　日付(日)　　　　売上金額 2013/08/26　　2013/09/01　　　　　3000 2013/09/02　　2013/09/08　　　　　6000 　　・ 　　・ 　　・ 2013/09/30　　2013/10/06　　　　　1000 ------------------------------------ このようにするにはどのようなSQLを組めばいいのでしょうか？ SQL初心者のため、難しい単語などを使わずに ご説明をしていただければと思います。 どうぞ、よろしくお願いいたします。

  • SQL文でクロス集計をしたい

    アクセスでクロス集計のSQL文で作りたいと思っています。 このようなデータがあります 年月 支払先 数量 金額 201301 A 10 1000 201302 A 5 500 201302 B 20 2000 201301 C 10 3000 201303 C 15 1500 このデータを下記のように表示したいのですが、 分からなくて困っています。 支払先　　データ　　　201301 201302 201303 A 数量　　　　　10　　　　　 5 A 金額 1000 500 B 数量　　　　　 20 B 金額 2000 C 数量　　　　　10 15 C 金額 3000 1500 合計 数量 20 25 15 合計 金額 4000 2500 1500 どのようにすれば実現出来ますか？ 今は、 SELECT 支払先, "数量" AS データ, Sum(IIf([年月]='201301',[数量],0)) AS 201301, Sum(IIf([年月]='201302',[数量],0)) AS 201302, Sum(IIf([年月]='201303',[数量],0)) AS 201303, FROM T_データ GROUP BY 支払先; のクロス集計を 数量と金額、数量の合計と金額の合計でクロス集計をしたクエリを UNIONで実現しています。 出来れば数量と金額は1つのクエリで実現したいと思いますが方法があれば教えて頂きたい と思います。 どうぞ、よろしくお願い致します。

  • 集計のSQL文を教えてください

    集計で困っています。 助けてください。 uriage_date(date型)　uriage_kingaku 2005/01/01 00:15　　　2000 2005/01/01 12:20　　　2500 2005/01/02 00:00　　　1000　←ここは前日分(2005/01/01)に 2005/01/02 05:15　　　1800 2005/01/04 12:30　　　2300 ～ 2005/12/31 20:20　　　5000 2005/12/31 22:45　　　1200 上の様なテーブルから 売上合計（1月分） uriage_date　　　　　goukei_kingaku 2005/01/01　　　　　　5500 2005/01/02　　　　　　1800 2005/01/03　　　　　　0 ～ 2005/01/31　　　　　　9000 この様に1日毎(当日00:01～翌日00:00)の売上の合計金額 を一ヶ月単位で集計したいのですが、ＳＱＬ文が解りません。 00:00の売上は前日の合計に入れたいです。 データの無い日も0円として欲しいです。 よろしくおねがいします。

もっと見る

その他の回答 (5)

  • 回答No.6

まず”鉛筆”３０，１０の購入者の部分の内容を調べてみては？ 通常購入者が同じNULLなら集計されるはず。 表示されない、スペースなどが入っていませんか？ Select文を実行する前に"SET NULL _"などNULLを みれるようにして実行してみては？

共感・感謝の気持ちを伝えよう！

質問者からの補足

質問文が分かりづらかったかもしれませんが、 今回、NULLのものについては集計したくないので、質問をさせていただきました。 疑問点の本筋から外れるため、質問文には書いていなかったのですが、 購入者の部分については、実際にはもう少し厳密に、 TRIM(購入者)がNULLかどうか、という扱いをしています。 ちなみに、SET NULLについては、知らない知識だったので1つ勉強になりました。私は、まだまだ知識が浅いなあと思いました。 ありがとうございました。

  • 回答No.4

Ａさん Ｂさん の集計部分と購入者が空欄のものを別のSELECTにして、UNIONでくっつけちゃだめ？

共感・感謝の気持ちを伝えよう！

質問者からのお礼

回答ありがとうございます。 私も、真っ先にその方法を思いついたのですが、 それはしないで欲しいということになったため、 今回質問をさせていただいた次第です。 今回は、他の方のアイディアをいただくことにしました。

  • 回答No.3

No.2です。 すみません、先ほどSQLを書き忘れました。 select 品名, sum(金額), 購入者 from 売上テーブル group by 品名, 購入者, rollup(金額) having( grouping(金額) = 0 and 購入者 is Null) or (grouping(金額) = 1 and 購入者 is not Null) ;

共感・感謝の気持ちを伝えよう！

質問者からのお礼

いただいたSQL文で、ばっちり動きました！ ただ、今回のプログラムを、今後他の人がメンテナンスする可能性があるため、 比較的、誰にでも分かりやすい方法をとることにしました。 （私の周囲には、ROLLUPなどを使いこなせる人がいません。私もこの回答をいただくまで知りませんでした。） 一つの結果を出すにも、方法が何通りもあるものですね。 いろいろと勉強にもなりました。 本当にありがとうございました。

  • 回答No.2

group by grouping sets ((品名, 購入者), (品名, 金額, 購入者)) や group by grouping rollup(品名, 金額, 購入者) などを使って 該当する条件をhavig句で指定すれば、できないことはないのですが、かなり複雑で解りづらいSQLになってしまいます。何か良い方法がありましたら、また書き込みします。

共感・感謝の気持ちを伝えよう！

  • 回答No.1

売上テーブルの主キーを加工した値を集計条件に含めてください。 DECODE(購入者, NULL, 主キー) 見たいな感じで。 そうすれば、購入者がNULLのレコードはDECODEの結果が主キーに、購入者がNULLでなければNULLになるので お望みの結果が得られるかと。 一意に特定できるような項目がないのであれば、不可能かと。シーケンスとか使えばできないことはないかもしれませんが。

共感・感謝の気持ちを伝えよう！

質問者からのお礼

早速の回答ありがとうございます。 自分の周囲の人に聞いても良い案がなかったので、 正直あきらめ半分で質問させていただいたのですが、 たくさんの回答をいただいたので、うれしいです。 今回は、集計条件に主キーを含めるというアイディアを使わせていただきます。 NO５の方のアイディアと合わせて、 group by 品名, 購入者, nvl(購入者, 主キー) という感じにしました。 仕事で必要だったため、とても助かりました。 ありがとうございました。

関連するQ&A

  • SQLでの集計について

    こんにちわ。以下のようなテーブルから条件に見合う合計を出したいと思いSQLを書いてみたのですが、 思った結果になりません。 テーブルA ID1　得意先Cd　営業所Cd　日付 1　　001　　　　001　　2005/09/01 2　　001　　　　002　　2005/09/01 3　　001　　　　001　　2005/09/03 テーブルB ID2　ID1　部署　数量　金額 1　　1　　01　　1　　1000 2　　1　　01　　1　　1000 3　　1　　02　　1　　1000 4　　2　　01　　1　　1000 5　　2　　01　　1　　1000 6　　3　　01　　1　　1000 7　　3　　01　　1　　1000 8　　3　　02　　1　　1000 ［抽出結果］ 得意先Cd　営業所Cd　日付　　部署　数量(Sum)　金額(Sum) 001　　　　001　　2005/09/01　01　　　2　　　2000 001　　　　001　　2005/09/01　02　　　1　　　1000 001　　　　001　　2005/09/03　01　　　2　　　2000 001　　　　001　　2005/09/03　02　　　1　　　1000 001　　　　002　　2005/09/01　01　　　2　　　2000 ［SQL］ select 得意先Cd，営業所Cd，日付，部署，SUM(数量) As '数量計'，SUM(金額) As '金額計' from テーブルA AA Inner join テーブルB BB ON (AA.ID1 = BB.ID1) group by 得意先Cd，営業所Cd，日付，部署，数量，金額 order by 得意先Cd，営業所Cd，日付，部署 このSQLを実行すると、日付、部署共に同じ場合でも数量・金額が合計値となりません。 得意先Cd　営業所Cd　日付　　部署　数量　金額 001　　　　001　　2005/09/01　01　1　　1000 001　　　　001　　2005/09/01　01　1　　1000 どのように変更を加えれば、上記の「抽出結果」にできるのでしょうか？ よろしくお願いいたします。

  • UNION ALLでつなげた複数ビューの集計

    お世話になっています。 現在下記のようなSQLを組んでいます。 ビュー１の集計結果 UNION ALL ビュー2の集計結果 UNION ALL ビュー3の集計結果 実行結果　例 年月 　　　 人数　　金額 201104　　　3 　　　20000 201105　　　2 　　　10000 201104　　　1 　　　5000 GROUP BY句で年月を集計したビューをUNION ALLでつないでいるため、 当然のように同じ年月が何度も出てくるため、テストがしづらくなっています。 やりたいこととしては、さっきの実行結果をさらに年月で集計することを 考えています。 年月 　　　 人数　　金額 201104　　　4 　　　25000 201105　　　2 　　　10000 対策として、下記のように集計したビューをUNION ALLでつないだ結果を さらに集計するSQLを作りました。 SELECT　 FROM（ ビュー１の集計結果 UNION ALL ビュー2の集計結果 UNION ALL ビュー3の集計結果 ） GROUP　BY その結果、下記のエラーが発生しました。 ORA-00935:グループ関数のネスト・レベルが深すぎます。 環境はoracle11gです。 あとは、SQLのFROM句の（）の部分をビューとして作成し、 更にそのビューを呼び出して集計するくらいしか思いつかないです。 今回作ろうとしてるのはテスト用のSQLのため、SQL文と実行結果を残したいので、 できればビューやプロシージャーは作らずに、SQLのみで作成したいと考えています。 何か良い方法はありますでしょうか？ 宜しくお願い致します。

  • 売り上げ集計SQLが作れません

    １つのTABLEに、1日の売上げ高を商品ごとに登録していくDBがあります。 簡単な構造は以下のとおりです。 日付　　　　 　 |A品売上額 　|B品売上額 | 2008/11/01 |500000　　　 |600000　　　| 2008/11/02 |450000　　　 |800000　　　| 2008/11/03 |100000　　　 |700000　　　| ・・・ 2008/11/30 |200000　　　 |500000　　　| 2008/12/01 |300000　　　 |100000　　　| ＜質問＞ それぞれの商品の1ヶ月売上高をSQLで抽出したいと思うのですが、Group byで何を指定したらいけるのかわからず困っています。 SQLで計算せずに抽出だけ行って、CGI側で計算するしかないのでしょうか？ アドバイスお願いいたします。 ＜最初に考えたSQL（Group byがないためエラーになりました）＞ select sum(a),sum(b) from uriage where date between '2008/11/01' AND '2008/11/30';

  • SQLで集計について分からないことがあります。

    SQLで集計について分からないことがあります。 まず、次のようなテーブルがあったとして 日付　 店舗コード　ブランドコード　金額 ---------------------------------------- 09-01 | 401101 | 01 | 10000 09-02 | 401101 | 01 | 20000 09-03 | 401101 | 01 | 30000 09-04 | 401101 | 01 | 40000 09-01 | 401101 | 02 | 10000 09-02 | 401101 | 02 | 20000 09-03 | 401101 | 02 | 30000 09-04 | 401101 | 02 | 40000 09-01 | 401101 | | 11111 09-02 | 401101 | | 22222 09-03 | 401101 | | 33333 09-04 | 401101 | | 44444 ブランドコードが'Null'の金額をブランドコードが'01'の金額に 日付をキーにして集計したいんですが、 可能でしょうか？ なにか、良い方法があればお願いします。

  • 年齢分布テーブルの再集計SQL

    テーブルA 年齢,人数 20,15 21,11 22,26 .... 30,5 31,88 ... 60,5 上記テーブルAからのSQLで下記のデータが欲しいと思っております。 【欲しい結果】 年齢範囲,人数 20～24,50 25～29,33 30～34,199 ... ただし、 select '20～24'as 年齢範囲,count(*)as 人数 from テーブルA where 条件1 union select '25～29'as 年齢範囲,count(*)as 人数 from テーブルA where 条件2 union select '30～34'as 年齢範囲,count(*)as 人数 from テーブルA where 条件3 ... というSQLは使いたくないです。(テーブルAの記述が長めなので、同じものを複数記述したくないのです) また結果が 項目名：20～24,25～29,30～34,... レコード：50,33,199,... というのも遠慮したいです。 CASE文やsum、groupを使えば、できそうな気がするのですが、、、 ご教授お願い致します。

  • SQLで部分的にGROUP BYしたいとき

    はじめまして。 SQLで部分的にGROUP BYで集計したいのですが、 どうもしっくりくるSQLがかけません。 多分CASEあたりを使うと綺麗で高速なSQLがかけると思っています。 皆様のお知恵をお借りしたく投稿しました。 具体的には以下のような出納帳データで 出納帳 日付　　　　　金額 2012/8/20　1000 2012/8/20　2000 2012/8/21　-1000 2012/8/21　-2000 2012/8/22　3000 2012/8/23 4000 2012/8/24 -3000 2012/8/24 -4000 2012/8/27 5000 ↓集計 日付　　　　　金額 2012/8/20　1000 2012/8/20　2000 2012/8/21　-3000(-1000と-2000を集約) 2012/8/22　3000 2012/8/23 4000 2012/8/24 -7000(-3000と-4000を集約) 2012/8/27 5000 というように、マイナスの金額は集約してしまいたいのですが、どのようなSQLが最適でしょうか？ とりあえず、UNIONかなと思い、 (SELECT 日付, 金額 FROM 出納帳 WHERE 金額>=0 UNION ALL SELECT 日付, 金額 FROM 出納帳 WHERE 金額<0 GROUP BY 日付 ) ORDER BY 日付 とプラス金額とマイナス金額にわけUNIONしたのですが、 もっと綺麗(高速)にやる方法があるのではないか？と思い投稿いたしました。 なにかヒントございましたら、ご教授ください。 DB環境：Oracle 11g

  • SQLの集計について教えてください。

    環境はoracle9iです。 ■テーブルの構成 |年度|月|部署コード|商品コード|金額| ■テーブルの内容 |2010|01|AAA|001|1000| |2010|02|AAA|001|2000| |2010|01|AAA|001|3000| |2010|01|AAA|002|3000| |2010|02|BBB|003|4000| |2010|01|CCC|001|5000| |2010|03|CCC|001|6000| 上記のようなテーブルがあり、 これを年度,月,部署コード,商品コードでサマリした値を表示したいです。 と、ここまでなら上の4つでGROUP BYしてあげれば良いのですが、 そこに年度,部署コード,商品コードでサマリした 「累計金額」列を加える必要があります。(イメージは下記のとおりです。) ■抽出結果の構成 |年度|月|部署コード|商品コード|金額|累計金額| ■抽出結果の内容 |2010|01|AAA|001|4000|6000| |2010|02|AAA|001|2000|6000| |2010|02|BBB|003|4000|4000| |2010|01|CCC|001|5000|11000| |2010|03|CCC|001|6000|11000| GROUP BYと別の集計単位で列を作ることが可能か、 可能であればどのようなSQLを書くべきか、 というところを教えてください。

  • SQLの集計

    「商品」テーブルについて商品コードごとにカウントしたいです。 ただ、「商品」テーブルには「商品コードA」と「商品コードB」がある。 「商品コードB」が空欄の場合は、「商品コードA」としてカウント。 「商品コードB」が空欄でない場合は、「商品コードB」としてカウントし、商品コードAの数に含めない。 ※「商品コードA」が空欄の場合はありえない。 これをSQL文でどう表現しますか？ よろしくお願いします。

  • MySQLで対象期間を決め集計しランキングする方法

    MySQLの初心者ですが、対象期間で集計を行いランキング付けし任意の上位何位までを抽出する方法について教えてください。 全データから集計後上位5位を選ぶ場合・・同順有り select x.`品名`,x.kei,(select count(*)+1 from (select `品名`,sum(`金額`) as kei from uriage group by `品名`) y where x.kei<y.kei ) as rank from (select `品名`,sum(`金額`) as kei from uriage group by `品名`) x having rank <= 5 order by rank といろいろ参考にしてで出来ましたが。任意の対象期間（日付）を設定して実行するSQL文が思いつきません。どこに期間を挿入するのか・・・悪あがきで思い付いた方法として CREATE OR REPLACE VIEW uriage_01 AS SELECT `品名`,SUM(`金額`) AS `金額` FROM uriage WHERE `日付` BETWEEN "2001/01/01" AND "2001/01/31" GROUP BY `品名` で任意期間の集計結果のビューを作りそのビューから select r1.`品名` as `品名`, r1.`金額` as p, count(r2.`金額`)+1 as rank from uriage_01 as r1 left join uriage_01 as r2 on r1.`金額` < r2.`金額` group by r1.`品名` having rank <= 5 order by rank と手間の掛かる方法です。単純に5行だけに限定ならorder by `金額`Limit 5 にすれば良いだけですけど・・・同順があるとどうすれば良いのかと思考停止状態です。尚、当方は、MySQLの勉強を始めたばかりにですので宜しくお願いいたします

    • ベストアンサー
    • MySQL
  • ＳＱＬ文で、1,2,3、以外（0とかブランク）を1と見立てて集計したい

    データを取り出す時次のように書きます SELECT テーブル1.id, テーブル1.kubun FROM テーブル1; このフィールドkubun、は本来、1、か、2、ですが 例外的にブランクや0（ゼロ）が入っています。 フィールドkubun、がブランクやゼロだったら、1に変換 しながら、テーブル1から集計するＳＱＬ文はないでしょうか？ テーブル1を前もって変換しておけが、いいのですが そうした方法を使わず、集計の時に行いたいのですが

カテゴリ

専門家に質問してみよう

職業から探して質問する