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

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

本文(発行内容)

＜SQL HAVING句でSUMした値をチェックする＞
こんにちは、三流プログラマーのＫｅｎ３です。
レンタルサーバーもなんとか復旧したみたいです。
※エラーの連絡あって、復旧の連絡無しかよ
　と、読者からキツイ一言ももらいつつ、（オイオイ）

今回は、SQLのHAVING句で集計関数SUMの値をチェックしてみます。

/*
 * １．今回のキッカケ
*/

掲示板 で 下記の質問をもらいました。
（メルマガ作者のジレンマ：心の中の右脳左脳バトル
　質問の回答も先入れ先出しダロ、前の質問はクリアしたのかよ。
　※前の彼女との問題クリアしてないのに次に行くか？オイ・・・
　　簡単な問題に手をつけて、難しい問題は先送り・・それでいいの？
　　と心の中で思いつつ、発行してしまいました）

-----
タイトル：数量0のときの項目削除
集計関数を使った表の作成で、例えば、数量が0の項目を表示しない
ようにするには、どのようにＳＱＬを書けば、いいのでしょうか？
-----
に対して、
最近、勘違い回答が多い私は、
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-----
投稿者名：Ken3(管理者)
タイトル：Re: 数量0のときの項目削除

こんにちは。
現在０が表示されているSQL文
か
集計元の表を教えていただけると、
解答しやすいです、回答できるかもしれません。
（最近、私、勘違い回答多くって・・・）
普通にSum(XXX)で集計値が０のレコードなら、
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
なんて感じで複数項目でフィールドを消すのか？
それとも、クロス集計のクエリーで０項目をカットするのか？
など、読みきれなかったので。
----

と、自信の無い回答（笑）


/*
 * ２．集計値が０の行は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は、０だから、表示したくない。
※実際の表でもありがちなのが、１０月から取引を始めた会社なんかは、
　上期の表に０を出したくない。
　また、残念だけど、８月で取引終了、下期の表には０で載せたくない。
　そんな流れを勝手に想像。
※取引先じゃなくて、夏物商品・冬物商品と期間限定商品あったら、
　マスターにレコードあっても出したくない、そんな状況はありかなぁ。

んっ？どこかでみた表だなぁと思ったら、
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でつなげてました。

テストデータで
Ｂ１の売上データ４月－９月を０にして明細を作成します。
Ｂ２は、４月－９月の売上アリで、Ｂの小計は表示させる
あと、
グループＤを追加して
Ｄ１ コート
Ｄ２ マフラー
と冬物商品を作成して、４～９月の売上無し、１０月～３月の下期に売上を作成。
上期の表には、グループ小計も出したくない、
そんな処理をやってみます。

/*
 * ３．Whereで０を排除する
*/

普通に発行したSQLを発行して、集計すると
http://www.ken3.org/cgi-bin/test/test076-2.asp
みたいに、レコードがあるのでSUMで集計して０のデータも表示されます。
※テーブルの作り構造問題は別にして、考えてくださいね。


↑０データが表示されている表イメージ

さてと、上期の合計が０の場合、表示したくない。
そんな条件を入れてみますか。

条件？
そうですね、条件を入れてみます。

だったらWhereに書くの？
そうですね、Whereに条件を追加してみます（ANDで１つ増やします）

上期合計 > 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
と、上期が０以上なんて感じでＯＫと思いテストを行います。

すると、
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
で、テスト実行すると、
上期０のデータ行は、表示されなくなりました。
が、
まだ、小計が０のＤグループが表示されてます。


↑０の明細は消えたが、０のＤ小計が表示されている表イメージ

/*
 * ４．SUMの集計値を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'

に追加するとしたら、同じようにやると、

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で追加すればＯＫでしょ簡単簡単。

オイオイ本気かよ？やってみな。
言われなくてもやってみるよ、ほらできたでしょ？

表示されたよ、下記のエラーが
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
でテストすると、やっと、目的の結果が得られると思います。

/*
 * ５．終わりの挨拶　＜／ＨＴＭＬ＞
*/

今回は、
Where句やHAVING句
で
０を取り除いた出力のSQLを軽く書きました。

いろいろと問題点を残しつつ、脱線・寄り道の多いメルマガですが、
今後とも、よろしくお願いします。

何かの参考となれば幸いです。

ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。

ページフッター

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

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

感想や質問・要望・苦情など 三流君へメッセージを送る。

下記のフォームからメッセージを送ることができます。

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

[三流君(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が使えるので、あまり使用しないけど、普通のテキストファイル処理
・[テキストファイル処理]・・・ファイルを開いて、書き込む。１行読み込みなどを軽く

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

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

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

本文(発行内容)