[三流君] −−＞
[ＡＳＰで遊ぶ、失敗する] −−＞
[バックナンバー一覧]
−−＞ No.088 SQL HAVING句でSUMした値をチェックする
SQL HAVING句でSUMした値をチェックする
本文(発行内容)
＜SQL HAVING句でSUMした値をチェックする＞
こんにちは、三流プログラマーのＫｅｎ３です。
レンタルサーバーもなんとか復旧したみたいです。
※エラーの連絡あって、復旧の連絡無しかよ
と、読者からキツイ一言ももらいつつ、（オイオイ）
今回は、SQLのHAVING句で集計関数SUMの値をチェックしてみます。
/*
* １．今回のキッカケ
*/
掲示板
http://www.ken3.org/cgi-bin/bbs/asp/wforum.cgi
で下記の質問をもらいました。
（メルマガ作者のジレンマ：心の中の右脳左脳バトル
質問の回答も先入れ先出しダロ、前の質問はクリアしたのかよ。
※前の彼女との問題クリアしてないのに次に行くか？オイ・・・
簡単な問題に手をつけて、難しい問題は先送り・・それでいいの？
と心の中で思いつつ、発行してしまいました）
-----
タイトル：数量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
でテストすると、やっと、目的の結果が得られると思います。
-【けんぞう！】---------------------------------------------------------
ASPが利用可能なレンタルサーバーをお探しのアナタ、
http://www.ken3.org/asp/server.html ← けんぞうも使っているサーバーの紹介
『おっIISでbasp21でメール送信、mdbも使えるよ』（三流ＰＧ：31歳）
------------------------------------------------------------------------
/*
* ５．終わりの挨拶 ＜／ＨＴＭＬ＞
*/
今回は、
Where句やHAVING句
で
０を取り除いた出力のSQLを軽く書きました。
いろいろと問題点を残しつつ、脱線・寄り道の多いメルマガですが、
今後とも、よろしくお願いします。
何かの参考となれば幸いです。
ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。
フィードバック
ASP系の→[掲示板]←を覗く、質問を書き込む
評価・感想
三流君の主なリンク先
[アクセスランキング]
[サイトマップ]
[リンク先・相互リンク先など]
Ken3の日記(weblog) --
[広告・副収入系]
[プログラマー業務の愚痴]
[VBA系の話題]
[ASP系の話題]
[コンビニ系ネタ]
[その他]
その他 宣伝広告