[三流君] −−> [ASPで遊ぶ、失敗する] −−> [バックナンバー一覧]
−−> No.088 SQL HAVING句でSUMした値をチェックする

SQL HAVING句でSUMした値をチェックする



本文(発行内容)


<SQL HAVING句でSUMした値をチェックする>

こんにちは、三流プログラマーのKen3です。 レンタルサーバーもなんとか復旧したみたいです。 ※エラーの連絡あって、復旧の連絡無しかよ  と、読者からキツイ一言ももらいつつ、(オイオイ) 今回は、SQLのHAVING句で集計関数SUMの値をチェックしてみます。

/* * 1.今回のキッカケ */

掲示板 で 下記の質問をもらいました。 (メルマガ作者のジレンマ:心の中の右脳左脳バトル  質問の回答も先入れ先出しダロ、前の質問はクリアしたのかよ。  ※前の彼女との問題クリアしてないのに次に行くか?オイ・・・   簡単な問題に手をつけて、難しい問題は先送り・・それでいいの?   と心の中で思いつつ、発行してしまいました) ----- タイトル:数量0のときの項目削除 集計関数を使った表の作成で、例えば、数量が0の項目を表示しない ようにするには、どのようにSQLを書けば、いいのでしょうか? ----- に対して、 最近、勘違い回答が多い私は、 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ----- 投稿者名:Ken3(管理者) タイトル:Re: 数量0のときの項目削除 こんにちは。 現在0が表示されているSQL文 か 集計元の表を教えていただけると、 解答しやすいです、回答できるかもしれません。 (最近、私、勘違い回答多くって・・・) 普通にSum(XXX)で集計値が0のレコードなら、 Select URL, Count(URL) As URLCNT From log GROUP BY URL HAVING Count(URL) <> 0 ORDER BY Count(URL) DESC とか、HAVINGだけど、 Select SUM(AAA) AS F_A, SUM(BBB) AS F_B なんて感じで複数項目でフィールドを消すのか? それとも、クロス集計のクエリーで0項目をカットするのか? など、読みきれなかったので。 ---- と、自信の無い回答(笑)

/* * 2.集計値が0の行はSelectしない */

質問者から、下記の回答をもらう | 商品名 | 4月 | 5月 | 6月 | 7月 | 8月 | 9月 |上期計 | ---------------------------------------------------------------- | A1  | 1 |  2 | 3 |  4 | 5 |  6 |  21 | ---------------------------------------------------------------- | A2   | 0 |  0 | 0 |  0 | 0 | 0  |  0 | ---------------------------------------------------------------- | A3   | 3 |  1 | 5 |  6 | 7 |  8 | 30 | ---------------------------------------------------------------- | 小計 | 5 |  3 | 8 | 10 | 12 | 14 | 51 | ---------------------------------------------------------------- | B1  | 0 |  0 | 0 | 0 | 0 | 0 |  0  | ---------------------------------------------------------------- | B2 | 5 |  3 | 7 |  8 | 9 | 10 | 42 | ---------------------------------------------------------------- | B3   | 6 |  4 | 8 | 9 | 10 | 11 | 48 | ---------------------------------------------------------------- | 小計  | 11|  7 | 15 | 17 | 19 | 21 | 90 | ---------------------------------------------------------------- | 総計 | 16| 10 | 23 | 27 | 31 |35 | 141 | ---------------------------------------------------------------- ---- なるほどね、A2とB1は、0だから、表示したくない。 ※実際の表でもありがちなのが、10月から取引を始めた会社なんかは、  上期の表に0を出したくない。  また、残念だけど、8月で取引終了、下期の表には0で載せたくない。  そんな流れを勝手に想像。 ※取引先じゃなくて、夏物商品・冬物商品と期間限定商品あったら、  マスターにレコードあっても出したくない、そんな状況はありかなぁ。 んっ?どこかでみた表だなぁと思ったら、 http://www.ken3.org/cgi-bin/test/test076-2.asp で、 UNIONでつなげてた表でしたね。 Select T_月別売上.商品CD, T_商品.商品名, 売上4,売上5,売上6,売上7,売上8,売上9, ([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計 From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD UNION ALL Select Left([T_月別売上.商品CD], 1) & 'ZZZZ' AS GroupCD , '小計' AS Dummy商品名 , Sum([売上4]) , Sum([売上5]) , Sum([売上6]) , Sum([売上7]) , Sum([売上8]) , Sum([売上9]) , Sum([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計 From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD Group By Left([T_月別売上.商品CD], 1) & 'ZZZZ' Order By T_月別売上.商品CD と、明細の表と集計値をUNIONでつなげてました。 テストデータで B1の売上データ4月−9月を0にして明細を作成します。 B2は、4月−9月の売上アリで、Bの小計は表示させる あと、 グループDを追加して D1 コート D2 マフラー と冬物商品を作成して、4〜9月の売上無し、10月〜3月の下期に売上を作成。 上期の表には、グループ小計も出したくない、 そんな処理をやってみます。

/* * 3.Whereで0を排除する */

普通に発行したSQLを発行して、集計すると http://www.ken3.org/cgi-bin/test/test076-2.asp みたいに、レコードがあるのでSUMで集計して0のデータも表示されます。 ※テーブルの作り構造問題は別にして、考えてくださいね。 ↑0データが表示されている表イメージ さてと、上期の合計が0の場合、表示したくない。 そんな条件を入れてみますか。 条件? そうですね、条件を入れてみます。 だったらWhereに書くの? そうですね、Whereに条件を追加してみます(ANDで1つ増やします) 上期合計 > 0 と0以上の文を追加してみます。 普通に考えると、 Select T_月別売上.商品CD, T_商品.商品名, 売上4,売上5,売上6,売上7,売上8,売上9, ([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計 From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD AND 上期計 > 0 ^^^^^^^^^^^^^^^^とANDにして、条件を追加します。 UNION ALL Select Left([T_月別売上.商品CD], 1) & 'ZZZZ' AS GroupCD , '小計' AS Dummy商品名 , Sum([売上4]) , Sum([売上5]) , Sum([売上6]) , Sum([売上7]) , Sum([売上8]) , Sum([売上9]) , Sum([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計 From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD Group By Left([T_月別売上.商品CD], 1) & 'ZZZZ' Order By T_月別売上.商品CD こんな感じで、 ([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計 で、上期計の項目を作成しているので、 AND 上期計 > 0 と、上期が0以上なんて感じでOKと思いテストを行います。 すると、 Microsoft JET Database Engine エラー '80040e10' 1 つ以上の必要なパラメータの値が設定されていません。 /cgi-bin/test/test088-1.asp, 行 63 えっ、なんで? 追加したのって、AND 上期計 > 0なんだけど。 この 1 つ以上の必要なパラメータの値が設定されていません ってエラー、経験上、フィールド名が間違えている時に出てくる。 日本語のフィールド名だから? う〜ん、今後の調査メモに入れておいて、 AND ([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) > 0 と、書いてかわしました。 http://www.ken3.org/cgi-bin/test/test088-1.asp で、テスト実行すると、 上期0のデータ行は、表示されなくなりました。 が、 まだ、小計が0のDグループが表示されてます。 ↑0の明細は消えたが、0のD小計が表示されている表イメージ

/* * 4.SUMの集計値をHAVING句でチェック0データをハジク */

さてと、グループ集計の0も表示させたくないよね。 Select Left([T_月別売上.商品CD], 1) & 'ZZZZ' AS GroupCD , '小計' AS Dummy商品名 , Sum([売上4]) , Sum([売上5]) , Sum([売上6]) , Sum([売上7]) , Sum([売上8]) , Sum([売上9]) , Sum([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計 From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD Group By Left([T_月別売上.商品CD], 1) & 'ZZZZ' に追加するとしたら、同じようにやると、 Select Left([T_月別売上.商品CD], 1) & 'ZZZZ' AS GroupCD , '小計' AS Dummy商品名 , Sum([売上4]) , Sum([売上5]) , Sum([売上6]) , Sum([売上7]) , Sum([売上8]) , Sum([売上9]) , Sum([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計 From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD AND Sum([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) > 0 Group By Left([T_月別売上.商品CD], 1) & 'ZZZZ' と、 And Sum([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) > 0 の条件をWhereにANDで追加すればOKでしょ簡単簡単。 オイオイ本気かよ?やってみな。 言われなくてもやってみるよ、ほらできたでしょ? 表示されたよ、下記のエラーが Microsoft JET Database Engine エラー '80040e14' WHERE 句 (T_月別売上.商品CD=T_商品.商品CD And Sum([売上4]+[売上5]+[売上6] +[売上7]+[売上8]+[売上9])) で集計関数を使用することはできません。 /cgi-bin/test/test088-2.asp, 行 64 (エラー報告にムッと来つつ) えっ、なんでだよ。 だって、Where句って、フィールドに対しての条件だから、 Sumとか集計関数は使えないんだってばさ。 そうなんだぁ、わかってたら言ってよ。 三流プログラマーのクセに調子コイテルから、実際のエラー見たほうがいいんだよ。 集計結果を絞り込むには、HAVING句に条件を書くんですよ。了解した? HAVING句?と驚いたフリをしつつ(オイオイ) 下記のようなSQL文になります。 流れ的にGroup Byでグループ化された結果に対して出力条件を付けたいときに、 HAVING句を使用します。 Select Left([T_月別売上.商品CD], 1) & 'ZZZZ' AS GroupCD , '小計' AS Dummy商品名 , Sum([売上4]) , Sum([売上5]) , Sum([売上6]) , Sum([売上7]) , Sum([売上8]) , Sum([売上9]) , Sum([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) AS 上期計 From T_月別売上, T_商品 Where T_月別売上.商品CD = T_商品.商品CD Group By Left([T_月別売上.商品CD], 1) & 'ZZZZ' HAVING Sum([売上4]+[売上5]+[売上6]+[売上7]+[売上8]+[売上9]) > 0 ^^^^^^ なんて感じで、HAVING句を記述します。 http://www.ken3.org/cgi-bin/test/test088-2.asp でテストすると、やっと、目的の結果が得られると思います。

/* * 5.終わりの挨拶 </HTML> */

今回は、 Where句やHAVING句 で 0を取り除いた出力のSQLを軽く書きました。 いろいろと問題点を残しつつ、脱線・寄り道の多いメルマガですが、 今後とも、よろしくお願いします。 何かの参考となれば幸いです。 ASP、VBScript勉強中の三流プログラマーのKen3でした。


ページフッター

ここまで、読んでいただきどうもです。目的の情報が見つかったか?少々心配しつつ、、、

三流君へ メッセージを送る

感想や質問・要望・苦情など 三流君へメッセージを送る。
返信例 XXXXさんへ
下記のフォームからメッセージを送ることができます。


あなたのお名前(ニックネーム):さん
返信は?:

アドレス:に返事をもらいたい
感想や質問↓:


(感想や質問・要望・苦情はHPで記事に載せることがあります。)

種類別のリンク や 広告など

[三流君(TOP ken3.org へ戻る)] / [ASPで遊ぶ、失敗する] / [ASP記事 バックナンバー目次]


Blogとリンク:[三流君のMemo別館]/ [ASP 三流君のソースコード置き場]/ [Ken3Video YouTubeで動画解説]
広告:

気になった ジャンル ↓を選択してください。

まぁ、基本はデータの受け取りかなぁ。
・[Form等を使用したデータのやり取り]・・・ASPと言っても、HTMLの入力フォームからデータを受け取ります。POSTやGETでやりとりを押さえますか。

次は、データの入出力 で ADOを使った(ADOで接続) と SQLの解説を少々
・[ADOでMdbファイルを使う]・・・MDBと接続して、簡単な追加・更新・削除を行った。
・[ADOでExcelと接続してみた]・・・.xlsと接続してSQLを使ってみた。
・[ADOでCSVと接続してみた]・・・.CSV テキストを読み出した。※更新・削除はできません

広告:



DBが使えるので、あまり使用しないけど、普通のテキストファイル処理
・[テキストファイル処理]・・・ファイルを開いて、書き込む。1行読み込みなどを軽く

VBScriptでFormat関数が無いなど、微妙にVBAと違うけど
[VBScript関数関係の説明]・・・少し、処理を書いてみた。
[その他処理サンプル]・・・あまり良いサンプル作れなかったけど。。。
何かの参考となれば幸いです。



[三流君(TOP ken3.org へ戻る)] / [ASPで遊ぶ、失敗する] / [ASP記事 バックナンバー目次]


広告: