SQLの書き方
解決オラクルを使って、データベースからあるデータを取ろうとしています。
それは、テーブルの年齢項目に11, 22, 25, 5, 45, 38才と様々なデータが年齢が入っているので、それを十代・20代、、、(1〜9歳、10〜19歳、20〜29歳）と年齢のグループに分けて、それぞれのグループ名前をつけて、データの件数を取得することです。
以下のコードは、sqlエラーになってしまいます。ただ、なかなかエラーを解消できないので、お伺いいたしました。
qry1.sql.add('select age, count(*) from table group by age where age between 10 and 19 as A, between 20 and 29 as B');
お手数ですが、アドバイスいただけないでしょうか？
CASE式のススメ
http://www.geocities.jp/mickindex/database/db_case.html
の「異なる条件の集計を一つのSQLで行なう」あたりはいかがですか？
素晴らしい情報をありがとうございます。ただ、残念なことに、私が使用しているオラクルは8iなので、9iからでないと使えないCASEを用いることはできません。
では、
10代の件数を取得するSQL文
20代の件数を取得するSQL文
．
．
で、１つずつ取得していくのは、だめですか？
はい、その書き方がわからないので、今回お伺いしています。理由として、それらをすべてTDBChartで１つのグラフに表示したいと考えているからです。
>10代の件数を取得するSQL文
>20代の件数を取得するSQL文
> ．
> ．
>で、１つずつ取得
と書きましたが、これをUNIONでつなげる方法はいかがですか？
select sum(age0) as 十歳未満,sum(age10) as 十台,sum(age20) as 二十台
from (
select 1 as age0, 0 as age10, 0 as age20 from table where age < 10
union all
select 0 as age0, 1 as age10, 0 as age20 from table where age between 10 and 19
union all
select 0 as age0, 0 as age10, 1 as age20 from table where age between 20 and 29
)
こんな感じでしょうか。
テストしてませんので、もしかしたら
"1 as age0" の部分を "cast(1 as integer) as age0" のように
データ型を指定しなければならないかもしれません。
(Paradoxではそうだった^^;)
年齢を10で割って整数化すれば10歳毎の幅が取れます。
この値でグループ化し、カウントすればOK
Age/10 -> 0 0〜9歳
Age/10 -> 1 10〜19歳
Age/10 -> 2 20〜29歳
:
Oracleでは関数"Trunc"が使えそうなので
select trunc(age / 10) as A, count(age) from tableA group by A;
で行けませんか？
グラフ項目名として使えそうな出力にするのも少し考えてみました。
年代では
select ((trunc(age / 10) * 10) || '歳代') as Generation, count(age) from tableA group by Generagion;
年齢幅付の項目名なら
select ((trunc(age / 10) * 10) || '歳〜' || (trunc(age / 10) * 10 + 9) || '歳') as RangeOfAge,
count(age) from tableA group by RangeOfAge;
のような感じでどうですか。
但し、10歳未満は、0歳代、0歳〜9歳となります。
ORACLE8iのSQLで、as aaaのように、 AS句で指定したものが、group byで使うことができません。列名が無効ですとのエラーがでてしまいます。アドバイスいただけないでしょうか？
GROUP BY句では別名不可だったような気がします。
DBによるのかな？
S&Hさんの例なら
GROUP BY age だとどうでしょうか？
違ったらすいません
では、これではどうでしょう？
Oracle9iでは通りましたが、8iでは分かりませんけど。
select A, count(*)
from (select trunc(age / 10) as A from tableA)
group by A
>AS句で指定したものが、group byで使うことができません。
あらま、そうでしたか。
では、
select trunc(age / 10) as A, count(age) from tableA group by trunc(age / 10);
とGroup Byにも同じ式を適用したらダメでしょうか。試してみてください。
>select trunc(age / 10) as A, count(age) from tableA group by trunc(age / 10);は、すでに実行してみたのですが、同じエラーが表示されてしまいました。
もう一つのやり方を試しましたら（questさんのやり方）、TQueryのTStringに書き込みましたら、うまくいったのですが、以下のコードをTbuttonのonclickに書き、実行したら「ORA-00937: 単一グループのグループ関数ではありません」というエラーが出てきました。
お手数ですが、修正すべき点を教えていただけないでしょうか？
注意：sql文の途中で改行されていますが、実際のコードは改行されていません。
with query１ do
begin
close;
sql.Clear;
sql.add('select sum(age0) as 十歳未満, sum(age10) as 十代, sum (age20) as 二十代');
sql.add(', sum(age30) as 三十代, sum(age40) as 四十代, sum (age50) as 五十代');
sql.add(', sum(age60) as 六十代, sum(age70) as 七十代, sum (age80) as 八十歳以上');
sql.add('from (select 1 as age0, 0 as age10, 0 as age20, 0 as age30, 0 as age40');
sql.add(', 0 as age50, 0 as age60, 0 as age70, 0 as
age80 from visitor where age < 10)');
sql.add(' union all select 0 as age0, 1 as age10, 0 as age20,
0 as age30, 0 as age40');
sql.add(', 0 as age50, 0 as age60, 0 as age70, 0 as age80
from visitor');
sql.add(', where age between 10 and 19 union all select
0 as age0, 0 as age10');
sql.add(', 1 as age20, 0 as age30, 0 as age40, 0 as age50,
0 as age60, 0 as age70');
sql.add(', 0 as age80 from visitor where age between
20 and 29 union all');
sql.add(' select 0 as age0, 0 as age10, 0 as age20,
1 as age30, 0 as age40, 0 as age50');
sql.add(', 0 as age60, 0 as age70, 0 as age80 from visitor
where age between 30 and 39');
sql.add(' union all select 0 as age0, 0 as age10, 0 as age20,
0 as age30, 1 as age40, 0 as age50');
sql.add(', 0 as age60, 0 as age70, 0 as age80
from visitor where age');
sql.add(', between 40 and 49 union all select 0 as age0,
0 as age10, 0 as age20');
sql.add(', 0 as age30, 0 as age40, 1 as age50, 0 as age60,
0 as age70, 0 as age80');
sql.add(' from visitor where age between 50 and 59
union all select 0 as age0');
sql.add(', 0 as age10, 0 as age20, 0 as age30, 0 as age40,
0 as age50, 1 as age60');
sql.add(', 0 as age70, 0 as age80 from visitor where age
between 60 and 69');
sql.add(' union all select 0 as age0, 0 as age10, 0 as age20,
0 as age30, 0 as age40');
sql.add(', 0 as age50, 0 as age60, 1 as age70, 0 as age80
from visitor');
sql.add('where age between 70 and 79union all select
0 as age0, 0 as age10, 0 as age20');
sql.add(', 0 as age30, 0 as age40, 0 as age50, 0 as age60,
0 as age70, 1 as age80');
sql.add(' from visitor where age > 80)');
sql.add('where ID = :i_ID');
parambyName('i_ID').asinteger := strtoint(editID.Text);
active := true;
end;
見づらいので、sql文をかきなおします。
with query１ do
begin
close;
sql.Clear;
sql.add('select sum(age0) as 十歳未満, sum(age10) as 十代,
sum(age20) as 二十代');
sql.add(', sum(age30) as 三十代, sum(age40) as 四十代,
sum(age50) as 五十代');
sql.add(', sum(age60) as 六十代, sum(age70) as 七十代,
sum(age80) as 八十歳以上');
sql.add('from (select 1 as age0, 0 as age10, 0 as age20,
0 as age30, 0 as age40');
sql.add(', 0 as age50, 0 as age60, 0 as age70,
0 as age80 from visitor where age < 10)');
sql.add(' union all select 0 as age0, 1 as age10, 0 as age20,
0 as age30, 0 as age40');
sql.add(', 0 as age50, 0 as age60, 0 as age70, 0 as age80
from visitor');
sql.add(', where age between 10 and 19 union all select
0 as age0, 0 as age10');
sql.add(', 1 as age20, 0 as age30, 0 as age40, 0 as age50,
0 as age60, 0 as age70');
sql.add(', 0 as age80 from visitor where age between
20 and 29 union all');
sql.add(' select 0 as age0, 0 as age10, 0 as age20,
1 as age30, 0 as age40, 0 as age50');
sql.add(', 0 as age60, 0 as age70, 0 as age80 from visitor
where age between 30 and 39');
sql.add(' union all select 0 as age0, 0 as age10, 0 as age20,
0 as age30, 1 as age40, 0 as age50');
sql.add(', 0 as age60, 0 as age70, 0 as age80
from visitor where age');
sql.add(', between 40 and 49 union all select 0 as age0,
0 as age10, 0 as age20');
sql.add(', 0 as age30, 0 as age40, 1 as age50, 0 as age60,
0 as age70, 0 as age80');
sql.add(' from visitor where age between 50 and 59
union all select 0 as age0');
sql.add(', 0 as age10, 0 as age20, 0 as age30, 0 as age40,
0 as age50, 1 as age60');
sql.add(', 0 as age70, 0 as age80 from visitor where age
between 60 and 69');
sql.add(' union all select 0 as age0, 0 as age10, 0 as age20,
0 as age30, 0 as age40');
sql.add(', 0 as age50, 0 as age60, 1 as age70, 0 as age80
from visitor');
sql.add('where age between 70 and 79union all select
0 as age0, 0 as age10, 0 as age20');
sql.add(', 0 as age30, 0 as age40, 0 as age50, 0 as age60,
0 as age70, 1 as age80');
sql.add(' from visitor where age > 80)');
sql.add('where ID = :i_ID');
parambyName('i_ID').asinteger := strtoint(editID.Text);
active := true;
end;
パット見ただけです
> sql.add(', between 40 and 49 union all select 0 as age0,
↑カンマ
> sql.add('where age between 70 and 79union all select
↑スペース
あとは見る気が起きません。
S&Hさんの方法は試しましたか？
はい。しかし、エラーの内容は、変わりませんでした。
sql.add(', 0 as age50, 0 as age60, 0 as age70,
0 as age80 from visitor where age < 10)');
サブテーブル作成部分の終端括弧の位置おかしくないですか？
すいません。位置じゃなくて不要じゃ？
かなり力技ですが・・・
Oracle 8iでもインラインビューは使えますよね？
select * from (select count(*) age0 from table where age between 0 and 9),
(select count(*) age10 from table where age between 10 and 19),
(select count(*) age20 from table where age between 20 and 29),
・
・
・
＜結果＞
AGE0 AGE10 AGE20
---------- ---------- ---------- ・・・
5 8 15
Delphiでプログラムしている訳ですから１０どか２０とか変数に
出来ないのでしょうか。
いろいろな情報ありがとうございます。
前回書いたsql文は、微調整したら、機能するようになりました。
ただ、where句で、項目のmonthのある一定の値をもっているデータを抽出するようにsql文を組むと、「ORA-01789 問い合わせブロックにある結果の列数が無効です」というエラーがでてしまいます。Delphiフォーム上のTEditに入力した値を項目month(数値型です）が持つデータを抽出したいと思っています。理由お分かりになるかたいらっしゃいますか？
with datamodule1.query1d do
begin
close;
sql.clear;
sql.add('select sum(age0) as 十歳未満, sum(age10) as 十代,
sum(age20) as 二十代, sum(age30) as 三十代, sum(age40)
as 四十代, sum(age50)');
sql.add('as 五十代, sum(age60) as 六十代, sum(age70) as 七十代,
sum(age80) as 八十歳以上 from (select 1 as age0, 0 as age10, 0
as age20, 0 as');
sql.add('age30, 0 as age40, 0 as age50, 0 as age60, 0 as age70,
0 as age80 month from table1 where age < 10 union all select
0 as age0, 1 as age10, 0');
sql.add('as age20, 0 as age30, 0 as age40, 0 as age50, 0 as
age60, 0 as age70, 0 as age80, month from table1 where age
between 10 and 19 union all');
sql.add('select 0 as age0, 0 as age10, 1 as age20, 0 as age30,
0 as age40, 0 as age50, 0 as age60, 0 as age70, 0 as age80,
month from table1 where age');
sql.add('between 20 and 29 union all select 0 as age0, 0 as
age10, 0 as age20, 1 as age30, 0 as age40, 0 as age50, 0 as
age60, 0 as age70, 0 as');
sql.add('age80 from table1 where age between 30 and 39 union all
select 0 as age0, 0 as age10, 0 as age20, 0 as age30, 1 as
age40, 0 as age50');
sql.add(', 0 as age60, 0 as age70, 0 as age80, month from
table1 where age between 40 and 49 union all select 0 as age0,
0 as age10, 0 as age20, 0 as');
sql.add('age30, 0 as age40, 1 as age50, 0 as age60, 0 as age70,
0 as age80, month from table1 where age between 50 and 59 union
all select 0 as age0');
sql.add(', 0 as age10, 0 as age20, 0 as age30, 0 as age40, 0 as
age50, 1 as age60, 0 as age70, 0 as age80, month from table1
where age between 60 and 69');
sql.add('union all select 0 as age0, 0 as age10, 0 as age20, 0
as age30, 0 as age40, 0 as age50, 0 as age60, 1 as age70, 0 as
age80, month from table1');
sql.add('where age between 70 and 79union all select 0 as age0,
0 as age10, 0 as age20, 0 as age30, 0 as age40, 0 as age50, 0 as
age60, 0 as');
sql.add('age70, 1 as age80, month from table1 where age > 80)');
sql.add('where month = :hensu_month');
parambyName('hensu_month').asInteger := strtoint(editmonth.Text);
active := true;
掲示したのが本物のソースだとしたら、monthの前にカンマがあったりなかったり
たまにmonth自体なかったりするのは問題じゃないの？
複数行に分けるにしても、せめてチェックしやすいように
規則性を持たせて区切ったらどうかと思うけど。
確かに、ちょっとみづらいですね。でも、tttさんが指摘しているのは、ちょっと違うっぽいですね。
（１）パット見 さんが指摘している
79union
が、そのまま。
（２）“0 as age20”で、as とage20の間に全角空白（１箇所）
（３）“from table1 where age between 30 and 39”
のときだけ、“, month”がない。
でしょうか？
tttさんの指摘個所
>0 as age80 month from table1 where age < 10 union all select
----------↑
しかし、"微調整したら、機能するようになりました"のSQL文をベースに改造すればこんなエラーはないはずなのに
新たに、SQL文を書き起こしたのでしょうか？
以前に指摘されたエラー個所がそのまま残っているところを見るとコピペしたとも思えるし。不思議だ。
ttt++さんへ、初めて長いSQL文を記載したときには、month項目をwhere句を使って指定することはしていませんでした。今回記載したのは、そのmonth項目をwhere句で指定してしたものなので、前回までのsql文にプラスしている形です。
問い合わせブロックにある結果の列数が無効です
このエラーはUNION時にフィールド数が合わないときにでるエラーだったと思います。構文ミスの可能性もあるので、まだエラーでてるようでしたらサブテーブルの部分だけで一度実行したらどうでしょうか？
それでエラーでなければ、そのSQLの両サイドにカッコつけてテーブルとして扱い集計関数を用いてSQLを実行してください。
※実行直前に組んだSQLをShowmessage等で表示して確認してみてはどうでしょうか？sql.add('・・・')の状態でにらめっこしてもわかりにくいですし。
いつも同じエラーがでてしまうので、確認させていただきたいのですけど、Questさんに教えていただいたこのSQL文にさらに、別のテーブル項目(month)を加えてwhere句で指定するためには、まずselect sum(age0) as 十代のカッコ（）の外にmonthを書き、そしてカッコ（）内のfrom table1の前に、それぞれmonthを加え、最後にカッコ（）の外のwhere句でmonthを指定するという考え方で間違ってないでしょうか(これは、すべてSQL.addのカッコ内の話をしています。）？
言葉で書かずに、SQLPlusで十呼応するSQL文を書かないと、非常に判りづらいですね。
Unionを使っているようですが、それぞれのSQL文の項目は同じ名前、同じ型でないとこんなエラーが出ます。
すみません。
お伝えしたかったことは、sql.add('select month←ここに記述, sum(age0) as 十歳未満, sum(age10)......);
sql.add('八十歳以上 from(select 1 as age0, 0 as age10, 0
as age20, 0 as age30, 0 as age40, 0 as age50, 0 as age60, 0
as age70, 0 as age80, month←ここに記述（他もfrom table1の前に 記述from table1 where age < 10....);
sql.add('where month = :hensu_month);
paramBYName('hensu_month').Asinteger = StrToInt(editMonth.text);
という考え方があっているかどうかということです。
sql.add('select');
sql.add(' sum(age0) as 十歳未満');
sql.add(' , sum(age10) as 十代');
sql.add(' , sum(age20) as 二十代');
sql.add(' , sum(age30) as 三十代');
sql.add(' , sum(age40) as 四十代');
sql.add(' , sum(age50) as 五十代');
sql.add(' , sum(age60) as 六十代');
sql.add(' , sum(age70) as 七十代');
sql.add(' , sum(age80) as 八十歳以上');
sql.add(' from (select');
sql.add(' 1 as age0');
sql.add(' , 0 as age10');
sql.add(' , 0 as age20');
sql.add(' , 0 as age30');
sql.add(' , 0 as age40');
sql.add(' , 0 as age50');
sql.add(' , 0 as age60');
sql.add(' , 0 as age70');
sql.add(' , 0 as age80');
sql.add(' , month');
sql.add(' from table1 where age < 10 ');
sql.add(' union all ');
sql.add(' select');
sql.add(' 0 as age0');
sql.add(' , 1 as age10');
sql.add(' , 0 as age20');
sql.add(' , 0 as age30');
sql.add(' , 0 as age40');
sql.add(' , 0 as age50');
sql.add(' , 0 as age60');
sql.add(' , 0 as age70');
sql.add(' , 0 as age80');
sql.add(' , month');
sql.add(' from table1 where age between 10 and 19');
sql.add(' union all');
sql.add(' select');
sql.add(' 0 as age0');
sql.add(' , 0 as age10');
sql.add(' , 1 as age20');
sql.add(' , 0 as age30');
sql.add(' , 0 as age40');
sql.add(' , 0 as age50');
sql.add(' , 0 as age60');
sql.add(' , 0 as age70');
sql.add(' , 0 as age80');
sql.add(' , month ');
sql.add(' from table1 where age between 20 and 29 ');
sql.add(' union all');
sql.add(' select');
sql.add(' 0 as age0');
sql.add(' , 0 as age10');
sql.add(' , 0 as age20');
sql.add(' , 1 as age30');
sql.add(' , 0 as age40');
sql.add(' , 0 as age50');
sql.add(' , 0 as age60');
sql.add(' , 0 as age70');
sql.add(' , 0 as age80');
sql.add(' , month '); //抜けている
sql.add(' from table1 where age between 30 and 39 ');
sql.add(' union all');
sql.add(' select');
sql.add(' 0 as age0');
sql.add(' , 0 as age10');
sql.add(' , 0 as age20');
sql.add(' , 0 as age30');
sql.add(' , 1 as age40');
sql.add(' , 0 as age50');
sql.add(' , 0 as age60');
sql.add(' , 0 as age70');
sql.add(' , 0 as age80');
sql.add(' , month ');
sql.add(' from table1 where age between 40 and 49 ');
sql.add(' union all ');
sql.add(' select ');
sql.add(' 0 as age0');
sql.add(' , 0 as age10');
sql.add(' , 0 as age20); //*全角スペースが入っていた
sql.add(' , 0 as age30');
sql.add(' , 0 as age40');
sql.add(' , 1 as age50');
sql.add(' , 0 as age60');
sql.add(' , 0 as age70');
sql.add(' , 0 as age80');
sql.add(' , month ');
sql.add(' from table1 where age between 50 and 59 ');
sql.add(' union all ');
sql.add(' select');
sql.add(' 0 as age0');
sql.add(' , 0 as age10');
sql.add(' , 0 as age20');
sql.add(' , 0 as age30');
sql.add(' , 0 as age40');
sql.add(' , 0 as age50');
sql.add(' , 1 as age60');
sql.add(' , 0 as age70');
sql.add(' , 0 as age80');
sql.add(' , month ');
sql.add(' from table1 where age between 60 and 69 ');
sql.add(' union all ');
sql.add(' select ');
sql.add(' 0 as age0');
sql.add(' , 0 as age10');
sql.add(' , 0 as age20');
sql.add(' , 0 as age30');
sql.add(' , 0 as age40');
sql.add(' , 0 as age50');
sql.add(' , 0 as age60');
sql.add(' , 1 as age70');
sql.add(' , 0 as age80');
sql.add(' , month ');
sql.add(' from table1 where age between 70 and 79');//79unionとくっついていた
sql.add(' union all ');
sql.add(' select ');
sql.add(' 0 as age0');
sql.add(' , 0 as age10');
sql.add(' , 0 as age20');
sql.add(' , 0 as age30');
sql.add(' , 0 as age40');
sql.add(' , 0 as age50');
sql.add(' , 0 as age60');
sql.add(' , 0 as age70');
sql.add(' , 1 as age80');
sql.add(' , month ');
sql.add(' from table1 where age > 80)');
sql.add(' where month = :hensu_month');
のように記述すれば、後から見るときに見やすくなり、バグの発見に繋がります。
この様にSQLを組み立てた後、sql.savetofileでテキストに落としてSQLPlusで確かめれば、どこがおかしいかよくわかります。
考え方は合っていると思います。
今回のようなサブテーブル作成するようなSQLの場合，最終のSQLでデバックしようとせず分解して潰していかないとどこでエラーが発生しているか分からなくなります。
サブテーブル化しているSELECT部分は一つのテーブルを作っているような物ですから，そこで失敗していればその後の集計関数を使用するSQLでも失敗するのは当然です。
あとソースはHOtaさんが記載されているように見易いソースを書くのをお勧めします。
見やすいソースとしてこんなことをしていますが・・・
HOtaさんのソースを切った張ったしていますので、一部インデントが狂っていますが、
一旦テキストの形で作成して、最終的に sql.add(SQL_St) で処理します。
var SQL_St
SQL_St :=
' select ';
+' sum(age0 ) as 十歳未満 , sum(age10) as 十代 , sum(age20) as 二十代 , sum(age30) as 三十代 , ';
+' sum(age40) as 四十代 , sum(age50) as 五十代 , sum(age60) as 六十代 , sum(age70) as 七十代 , sum(age80) as 八十歳以上 ';
+' from ( ';
+' select ';
+' 0 as age0 , 1 as age10 , 0 as age20 , 0 as age30 , 0 as age40, 0 as age50 , 0 as age60 , 0 as age70, 0 as age80, month ';
+' from table1 where age < 10 ';
+' union all ';
+' select ';
+' 0 as age0 , 1 as age10 , 0 as age20 , 0 as age30 , 0 as age40, 0 as age50 , 0 as age60 , 0 as age70, 0 as age80, month ';
+' from table1 where age between 10 and 19 ';
+' union all ';
+' select ';
+' 0 as age0 , 1 as age10 , 0 as age20 , 0 as age30 , 0 as age40, 0 as age50 , 0 as age60 , 0 as age70, 0 as age80, month ';
+' from table1 where age between 20 and 29 ';
+' union all ';
+' select ';
+' 0 as age0 , 1 as age10 , 0 as age20 , 0 as age30 , 0 as age40, 0 as age50 , 0 as age60 , 0 as age70, 0 as age80, month ';
+' from table1 where age between 30 and 39 ';
+' union all ';
+' select ';
+' 0 as age0 , 1 as age10 , 0 as age20 , 0 as age30 , 0 as age40, 0 as age50 , 0 as age60 , 0 as age70, 0 as age80, month ';
+' from table1 where age between 40 and 49 ';
+' union all ';
+' select ';
+' 0 as age0 , 1 as age10 , 0 as age20 , 0 as age30 , 0 as age40, 0 as age50 , 0 as age60 , 0 as age70, 0 as age80, month ';
+' from table1 where age between 50 and 59 ';
+' union all ';
+' select ';
+' 0 as age0 , 1 as age10 , 0 as age20 , 0 as age30 , 0 as age40, 0 as age50 , 0 as age60 , 0 as age70, 0 as age80, month ';
+' from table1 where age between 60 and 69 ';
+' union all ';
+' select ';
+' 0 as age0 , 1 as age10 , 0 as age20 , 0 as age30 , 0 as age40, 0 as age50 , 0 as age60 , 0 as age70, 0 as age80, month ';
+' from table1 where age between 70 and 79 ';
+' union all ';
+' select ';
+' 0 as age0 , 1 as age10 , 0 as age20 , 0 as age30 , 0 as age40, 0 as age50 , 0 as age60 , 0 as age70, 0 as age80, month ';
+' from table1 where age > 80 ';
+' where month = :hensu_month ';
皆さんどうもありがとうございました。今までのものを微調整したら、希望通りの処理ができるようになりました。
| ツイート |  |