質問をすることでしか得られない、回答やアドバイスがある。

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

ただいまの
回答率

90.98%

  • SQL

    2017questions

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

  • Oracle

    518questions

    Oracleは、米オラクルが取り扱うリレーショナルデータベース管理システムです。メインフレームからPCまで、多様なプラットフォームに対応しています。

OracleSQLのPIVOT関数を使って各科目(行)の成績分布(列)を表示し、かつそれらの数字の合計をカラムの一番右に追加したい。

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 211

前提・実現したいこと

以下の4つのテーブルがあります。

1
DEPARTMENT_TO_MAJOR: 大学の学部(または専攻)のコード(DCODE)と名前(DNAME)

2
Course:大学の講義のコードと講義名を持つ
科目コード、コース名

3
Section: 大学のセクション(section_id)のコードと所属している科目のコード(course_num)+その他
***ここではセクション=各学期に開かれる講義と定義します。例えば、「数学1」という科目(Course)が2017年春と2017年秋に開かれたとき、それぞれの学期の講義を別々のセクションとして認識します。セクションが多、科目が一の関係です)。

4
GRADE_REPORT: 学生番号(student_number)とその学生が履修したOR履修しているセクションのコード(section_id)とその成績(grade:履修中は空欄)を持つ。

以下のようなテーブルを作成したいです。

学部名 科目コード 科目名 A B C 履修生徒数
chemistry 001 organic chemistry 3 5 5 16
biology 002 genetics 2 1 0 10
physics 003 quantum physics 0 3 3 6

科目をすべて列挙して、その科目を取った学生のGradeをGrade別にカウントして、さらにその合計を一番右に表示したいです。ただし、以下のような条件があります。

  1. まだどの学生も、その科目のGradeを持ってない場合は、その科目は表示しません。
  2. 一人の生徒は同じ科目を何度も受けられるとします(別セクションで)。その際、Gradeはそれぞれカウントします(e.g. CとBをとったらそれぞれ数えます)。
  3. もし、Grade Dをとった生徒の数またはGrade Fをとった生徒の数がGrade Cをとった生徒の数より多かった場合は、その科目を表示しません。
  4. Dept Name, A, B, C, D, F, Course Numberの順でソートします(A, B, C はDesc、それ以外はAcsです)。

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

自分でいろいろ試してみて、とりあえず、カラムだけは目的のテーブルと同じものができました。ただ、PIVOTがうまくいかず、Gradeの値とその合計がとれずすべて0になってしまいます。

今の私のコードの問題として以下があると思っているのですが、それをどうやって解決すればよいのかわかりません。。。

  1. Inline selectの中のSQLではGradeのデータが取れているのに、Pivotではなぜかカウントされない。
  2. 一番右のカラム"TotalEnroll"がPivotが分類する要素の一つになっていて合計をとるようになってない。

試したこと

  1. Pivot関数の中やその前のSelect句やInlineの別名をいろいろ変えてみる。

  2. 下記のページを参考にいろいろ試してみる。でもうまくいきませんでした。
    https://community.oracle.com/thread/1094307
    https://www.insight-tec.com/mailmagazine/ora3/vol422.html

  3. 一番そとのSelect句で、具体的な列名を指定すると以下のエラーが出る(アスタリスクなら出ません)。
    ORA-00904: "NumOfEnrollEachCo"."TotalEnrollEachCourse": invalid identifier
    00904 00000 -  "%s: invalid identifier"
    *Cause:    
    *Action:
    Error at Line: 11 Column: 7

  4. Inline selectですでに取得したGradeの合計値を
    ,"NumOfStEachCo"."numOfStEachCo" as "TotalEnrollEachCourse"
    と書いて外のSQLでも使えるようにすると、そのカラムが”A”カラムの左に来てしまう。

  5. Inline selectですでに取得したGradeの合計値を
    ,"NumOfStEachCo"."numOfStEachCo" as "TotalEnroll"
    と書いて外のSQLでも使えるようにすると、以下のエラーが発生する。
    ORA-00918: column ambiguously defined
    00918 00000 -  "column ambiguously defined"
    *Cause:    
    *Action:
    Error at Line: 4 Column: 5

補足情報(言語/FW/ツール等のバージョンなど)

Oracle 12c を使ってOracle Developer上で動かしています。
ほかに何か必要な情報があれば、すぐに用意します。

どうか、よろしくお願いいたします。

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

check解決した方法

0

「質問時に間違っていたPIVOTの使い方」
1PIVOTが呼ぶクエリのSelectでエイリアスをつけていた(つけているとエラーが出る)
2PIVOT関数内のfor の後ろには、PIVOTで分類されるデータを持ったカラムを設定する。
2PIVOT関数内のfor の前には、PIVOTで分類されるデータの集計方法を設定する。

例えば、以下のように書く。
pivot(count(GRADE) for GRADE in ('A' as A ,'B' as B,'C' as C)

「PIVOTで集計されたカラムの後ろに別のカラムを足す方法」
Pivotで作成したテーブルを丸々Inline selectにして、別のテーブルとJoinさせる。表示するカラムは、一番外側のSELECTで設定する。

例:
select
p.title
,p.A
,p.B
,p.c
,o.other_data
from (

PIVOTするためのテーブル(Inline select)

) PIVOTTABLE p join OTHERTABLE 0 on (p.pk = o.pk)

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

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

  • ただいまの回答率 90.98%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • SQL

    2017questions

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

  • Oracle

    518questions

    Oracleは、米オラクルが取り扱うリレーショナルデータベース管理システムです。メインフレームからPCまで、多様なプラットフォームに対応しています。

  • トップ
  • Oracleに関する質問
  • OracleSQLのPIVOT関数を使って各科目(行)の成績分布(列)を表示し、かつそれらの数字の合計をカラムの一番右に追加したい。