SQLPlusのグループ関数と小計について

質問

Oracle9iのSQLPlusにて、日ごとのIDごとの小計をとりたいだけなのですが、
グループ関数の使い方がよくわかっていないのか上手くいきません。

以下のように出力したいと思い、以下のようなSQLを作成しましたが、、、、

＜最終形＞
入金日　 ID　　　名前　　請求開始日　請求終了日　合計金額
20080901 252 　あああ　20080701 20080731 17000
20080901 252 空白　　空白　　　　　空白 17000 ←　ID=252の小計
20080901 534 　いいい 20080601 20080630 18000
20080901 534 空白 　　空白　　　　　空白 18000 ←　ID=534の小計
20080901 空白 空白 空白　　 　　　空白　　　 　　35000　←　20080901の小計

＜SQL文＞
select 入金日,ID,名前,請求開始日,請求終了日,sum(入金額)
～
group by
rollup(入金日,ID,名前,請求開始日,請求終了日)

＜現状＞
入金日　 ID 　 名前 請求開始日　請求終了日　合計金額
20080901 252 あああ 20080701 20080731 17000
20080901 252 あああ 20080701　 空白 　　　　 17000 ←　不要
20080901 252 あああ 空白 空白 17000 　←　不要
20080901 252 空白 空白 空白 17000
20080901 534 　いいい 20080601 20080630 18000
20080901 534 　いいい 20080601 空白 18000 ←　不要
20080901 534 いいい 空白 空白 18000 ←　不要
20080901　空白　空白　　 空白 空白 　 　　35000

上記、SQL文ではrollupにて全項目を指定しているから当然なのかもしれませんが、
試しに、小計対象外を外に出して「group by 名前,請求開始日,請求終了日,rollup(入金日,ID)」としてみたりしましたが上手くいきませんでした。
要はグルーピング対象外および小計対象外の項目の扱い方がよくわかっていないのかと思います。
どなたかご存知の方がいらっしゃれば是非ご教示いただけませんでしょうか？

宜しくお願いします。

回答No.5

jamshid6 · ベストアンサー

サブクエリとは、基本的にSELECTした結果をテーブルと見立てて使うことです（Viewのようなイメージです）。

したがって、サブクエリ自体は完結したものであって(*)、それを使うメインクエリはサブクエリを構成している個別のテーブルには関知しません。
つまり、メインクエリは以下のようなレイアウトのテーブルに対してSELECTを実行しているのと等しいです。
（項目名はサブクエリだけを抜き出して実行したときの戻りの項目名です）
TK14_NYUUKIN_DATE,
TK14_KANJYA_NO,
TK01_NAME,
TK13_SEIKYUU_START,
TK13_SEIKYUU_END,
SUMAMT
これらの値を生み出しているサブクエリ内のテーブルとは関係がなくなっています。それがBなどのエイリアスを指定できない理由です。

そう考えていただくと、テーブルの項目名にSUM(xxxx)などという名前は使えませんよね。それがSUMAMTという別名を付けている理由です。

(*) 別に相関サブクエリというものがあり、それはこの説明には当てはまらないかもしれませんが、これは必要になったときに勉強していただければと思います。

回答No.4

jamshid6

なるほど、このクエリならそうなるでしょうね。
「最初のSELECTの見出しを元にORDERを掛ける」というのは、「UNIONした場合は、SELECTの要素である項目に対してではなく、実行された結果に対してしかOrderが切れない」という意味だったのですが、今回のクエリは文字列結合してしまっているので、戻り列は１つですね。
したがって、ORDER BY 1,2はエラーになります。

そのため、今回のケースで、文字列結合する時点で項目別の並び順を指定できるようにするためには、サブクエリにする必要があると思います。
以下の通りです。
（OracleはOrderをかけるときにNULLの方が大きいとみなされるはずですから、これでいけると思います）
SELECT
TK14_NYUUKIN_DATE || ',' ||
TK14_KANJYA_NO || ',' ||
TK01_NAME || ',' ||
TK13_SEIKYUU_START || ',' ||
TK13_SEIKYUU_END || ',' ||
SUMAMT
FROM
(SELECT
B.TK14_NYUUKIN_DATE,
B.TK14_KANJYA_NO,
C.TK01_NAME,
A.TK13_SEIKYUU_START,
A.TK13_SEIKYUU_END,
SUM(B.TK14_NYUUKIN_GAKU) SUMAMT
FROM TK13_SEIKYUUSYO A
INNER JOIN TK14_NYUUKIN B ON TK13_KANJYA_NO=TK14_KANJYA_NO AND TK13_SEIKYUU_DATE = TK14_SEIKYUU_DATE
INNER JOIN TK01_KANJYA C ON A.TK13_KANJYA_NO = C.TK01_KANJYA_NO
WHERE B.TK14_NYUUKIN_DATE >= &1 AND B.TK14_NYUUKIN_DATE <= &2 AND B.TK14_NYUUKIN_GAKU > 0
GROUP BY B.TK14_NYUUKIN_DATE,B.TK14_KANJYA_NO,C.TK01_NAME,A.TK13_SEIKYUU_START,A.TK13_SEIKYUU_END

UNION

SELECT
B.TK14_NYUUKIN_DATE,
B.TK14_KANJYA_NO,
NULL,NULL,NULL,
SUM(B.TK14_NYUUKIN_GAKU)
FROM TK13_SEIKYUUSYO A
INNER JOIN TK14_NYUUKIN B ON TK13_KANJYA_NO = TK14_KANJYA_NO AND TK13_SEIKYUU_DATE = TK14_SEIKYUU_DATE
INNER JOIN TK01_KANJYA C ON A.TK13_KANJYA_NO = C.TK01_KANJYA_NO
WHERE B.TK14_NYUUKIN_DATE >= &1 AND B.TK14_NYUUKIN_DATE <= &2 AND B.TK14_NYUUKIN_GAKU > 0
GROUP BY ROLLUP(B.TK14_NYUUKIN_DATE,B.TK14_KANJYA_NO)
)
ORDER BY TK14_NYUUKIN_DATE,TK14_KANJYA_NO

補足

いろいろと勉強になります。。

まず、メインクエリにて、TK14_NYUUKIN_DATE等の項目に「B.」とテーブル名を付けると、ORA-0904が発生しますね。。このテーブルの別名はサブクエリだけに有効となるのでしょうか？そもそも別名は不要ですか？
また、メインクエリのSUMAMTとは単なる別名でしょうか？でもメインクエリに SUM(TK14_NYUUKIN_GAKU)と書いてもやはりORA-0904が発生します。
これはなぜなんでしょうか？？
奥深いですね。。。

回答No.3

jamshid6

ORA-01785は戻りの列数が１つしかないのにORDER BY 2と切った場合などに出るエラーですが、当方では再現しません(11gですが）。
エラーの出るクエリを示していただけませんか。

補足

お手数おかけし申し訳ありません。
以下クエリです。

select
B.TK14_NYUUKIN_DATE || ',' ||
B.TK14_KANJYA_NO || ',' ||
C.TK01_NAME || ',' ||
A.TK13_SEIKYUU_START || ',' ||
A.TK13_SEIKYUU_END || ',' ||
SUM(B.TK14_NYUUKIN_GAKU)
from
TK13_SEIKYUUSYO A
inner join
TK14_NYUUKIN B
on
TK13_KANJYA_NO = TK14_KANJYA_NO
and TK13_SEIKYUU_DATE = TK14_SEIKYUU_DATE
inner join
TK01_KANJYA C
on
A.TK13_KANJYA_NO = C.TK01_KANJYA_NO
where
B.TK14_NYUUKIN_DATE >= &1
and B.TK14_NYUUKIN_DATE <= &2
and B.TK14_NYUUKIN_GAKU > 0
group by
B.TK14_NYUUKIN_DATE,
B.TK14_KANJYA_NO,
C.TK01_NAME,
A.TK13_SEIKYUU_START,
A.TK13_SEIKYUU_END
union
select
B.TK14_NYUUKIN_DATE || ',' ||
B.TK14_KANJYA_NO || ',' ||
null || ',' ||
null || ',' ||
null || ',' ||
SUM(B.TK14_NYUUKIN_GAKU)
from
TK13_SEIKYUUSYO A
inner join
TK14_NYUUKIN B
on
TK13_KANJYA_NO = TK14_KANJYA_NO
and TK13_SEIKYUU_DATE = TK14_SEIKYUU_DATE
inner join
TK01_KANJYA C
on
A.TK13_KANJYA_NO = C.TK01_KANJYA_NO
where
B.TK14_NYUUKIN_DATE >= &1
and B.TK14_NYUUKIN_DATE <= &2
and B.TK14_NYUUKIN_GAKU > 0
group by
rollup(B.TK14_NYUUKIN_DATE,
B.TK14_KANJYA_NO)
order by
1,2;

先ほどのエラーが発生します。
ちなみにorder byを外すと、小計が先頭に出力されてしまいます。

宜しくお願いします。

回答No.2

jamshid6

unionの場合は、unionで結合した最初のSELECTの見出しを元にORDERを掛けることになります。
したがって、最初のSELECTに使われている見出しが入金日、IDではなくなっているのではないでしょうか？
もしくは、私が書いたように「ORDER BY 1,2」と戻り値の列番号を指定する方法もあります。

補足

いずれのSELECTも見出しは同一です。
また、「ORDER BY 1,2」の場合、「ORA-01785:ORDER BY 項目は、SELECTリスト式の数値である必要あります。」エラーが発生してしまいます。

尚、「ORDER BY 1」は上手くいきますが・・・？？？
どうなってるのでしょうか？

回答No.1

jamshid6

ROLLUPはGROUP BYの対象に対して機械的に小計を算出してしまうものですので、目的の結果はシンプルには得られないと思います。

したがって、以下のいずれかの方法になるかと
・不要な小計を拾わない
SELECT 入金日,ID,名前,請求開始日,請求終了日,SUM(金額)
FROM テーブル
GROUP BY ROLLUP(入金日,ID,名前,請求開始日,請求終了日)
HAVING
(CASE WHEN 名前 IS NULL THEN 1 ELSE 0 END)
+(CASE WHEN 請求開始日 IS NULL THEN 1 ELSE 0 END)
+(CASE WHEN 請求終了日 IS NULL THEN 1 ELSE 0 END) NOT IN (1,2)

・小計は別につける
SELECT 入金日,ID,名前,請求開始日,請求終了日,SUM(金額)
FROM テーブル
GROUP BY 入金日,ID,名前,請求開始日,請求終了日
UNION
SELECT 入金日,ID,NULL,NULL,NULL,SUM(金額)
FROM テーブル
GROUP BY ROLLUP(入金日,ID)
ORDER BY 1,2

補足

amshid6さんいつもありがとうございます！

パーフェクトなご回答ありがとうございます。
union使えないかなぁ～と思っていましたので早速試してみました。
・・が、union後のselectにて、order by 入金日,IDでなぜかエラー(ORA-0904:無効な識別子です)が出てしまいます。
試しにunion後のselectのみを実行すればエラーは出ません。
unionにはなにか特殊な制限でもあるのでしょうか？？
ご存知でしたら教えていただけないでしょうか？
宜しくお願いします。