MySQLで合計と小計の計算方法を教えてください パート２

質問

MySQLで合計と小計の計算方法を教えてください パート２
name | kamoku　|　tensu 　　　　　　　 テーブル：testdb　
---------------------------
001　　　　英語　　　95
001　　　　国語　　　90
001　　　　理科　　　75
001　　　　社会　　　73
001　　　　算数　　　62
002　　　　英語　　　93
002　　　　国語　　　87
002　　　　理科　　　78
002　　　　社会　　　71
002　　　　算数　　　68
--------------------------------
30人位いるのですが、やりたい事は以下になります。
基準点数（好きな点数を入力）$kijyunt

sql = select sum(tensu) as stensu, sum(tensu)-(select count(*) * $kijyun from testdb) as hensa from testdb where tensu >= 70

全科目70点以上の点数を合計し、全体のレコード×70の点数を引き算したいのです。

しかしながらうまく表示しません。どこが悪いかご指摘お願いします。

回答No.4

dell_OK · ベストアンサー

ANo.2の補足に書かれたコードをＰＨＰで一通り組み立てるとこんな感じでしょうか。

省略されたと思われるデータベース接続部分とＳＱＬ生成部分を追加しています。
本来、画面から手入力されるのであろう基準点数の入力部分は省き $kijyun = 70 としています。
後はそのままですが、結果は正しく表示されました。
点数偏差 : -38点
レース数 : (何が表示されるのかわかりませんが、空白)
－－－－
<?php
$con = mysql_connect("localhost","user");
$sel = mysql_select_db("test");

$kijyun = 70;
$sql = "select sum(tensu) as stensu, sum(tensu)-(select count(*) * $kijyun from testdb) as hensa from testdb where tensu >= $kijyun";

$res = mysql_query($sql);

print("<table border=\"0\">");
print("<tr><td>");
print("<table border=\"1\">");
print("<tr><TH><font size=3>点数偏差</font></TH><TH><font size=3>レース数</font></TH></TR>");
while($col = mysql_fetch_array($res)) {
print("<tr>");
print("<TD>".$col[stensu]."</TD>");
print("<TD><font size=3>" . $col[hensa] . "点</font></TD>");
print("</tr>");
}
print("</table>");
?>
－－－－
それから、ANo.3のご回答にあるＳＱＬ（詳しい説明は省きますが、私は今、このＳＱＬを見て勝手な解釈をし、すばらしいと感じた、世間では一般的かも知れませんが、最近ＭｙＳＱＬをはじめた私には、今、新たに学んだ事ですので、とてもすばらしいと思うＳＱＬ）を利用すれば、無駄な部分を少し簡略化できます。

select
sum(tensu*(tensu>=$kijyun)) as stensu
,sum(tensu*(tensu>=$kijyun))-sum($kijyun) as hensa
from testdb

基準点数として減算している、レコード数×基準点数と言うのは、 where をなくしたこのＳＱＬでは全レコード数を対象としますので、カウントするまでもなく $kijyun をそのまま合計すればいいだけです。
ＳＵＭとＳＵＭを演算するのは、ＳＵＭの中で演算してもいいと思うので、さらに簡略化できます。

select
sum(tensu*(tensu>=$kijyun)) as stensu
,sum(tensu*(tensu>=$kijyun)-$kijyun) as hensa
from testdb

ただ、仕様（人間が意識している手順をおった動作）と、簡略化（あるいは、最適化）されたソースコード（確実な結果が最小コードや最速コードで得られるようにしたために「何をしているのかよくわからない」）の表現力と理解力にギャップが生じますのでそこまでしない方がいい場合もあります。

結局のところ、質問者様のソースコードにも、ＳＱＬにも問題はないような気がしています。
うまく表示されないかわりに、どのようなものが表示されるのでしょうか。

お礼

今回は、うまくいきました。ありがとうございました！

回答No.3

yambejp

>このようにしたほうがいいですよね。

だから・・・
具体的な数字で示してもらえないと、質問者さんが頭の中で考えているのと
こちらの提示するものがあっているかどうか判断できないんですよ

とりあえず、#2さんの計算根拠にのっとるとこんな感じで・・・
SET @kijyun=70,@goukaku=70;
select sum(tensu*(tensu>=@goukaku)) as stensu, sum(tensu*(tensu>=@goukaku))-(select count(*) * @kijyun from testdb) as hensa from testdb;

SQL文を組み立てる以前に、仕様を決める部分を強化したほうがよいですね

回答No.2

dell_OK

ANo.1の補足に書かれたＳＱＬを $kijyun = 70 として実行すると、
以下の結果が出ました。
stensu : 662
hensa : -38
これが正しい結果でしょうか。
hensa は 662 - 700 の結果です。

補足

ありがとうございます。
正しい結果です。

ＳＱＬの式があっているとなると、ＰＨＰで吐き出す指示ができていないんでしょうか。

$res = mysql_query($sql);

print("<table border=\"0\">");
print("<tr><td>");
print("<table border=\"1\">");
print("<tr><TH><font size=3>点数偏差</font></TH><TH><font size=3>レース数</font></TH></TR>");
while($col = mysql_fetch_array($res)) {
print("<tr>");
print("<TD><font size=3>" . $col[hensa] . "点</font></TD>");
print("</tr>");
}
print("</table>");

これでやっているんですけど・・・

回答No.1

yambejp

>全科目70点以上の点数を合計

select sum(tensu*(tensu >= 70)) as stensu from testdb

その他いまいち計算根拠がよくわかりません。
合計数から総件数×基準点しているところも何がしたいのかわからないし
それはtensu>=70を適用したいのかどうかとか、いまいちわからないです。

具体的に基準値が○○だった場合に、結果がどうなるのはず・・・
のような書き方の方が検証しやすいですね

補足

コメントありがとうございます。詳しく説明しますと、各教科70点を合格の基準と設定しております。
今は、1クラスしかないのですが、他の先生の分と学年で算出したいと思っております。
求めたい結果は、各科目70点以上の点数の合計と全体の科目数（レコード数）×70点を求め、その差分を求めます。
このときの70点未満は点数に入れません。
このときの結果によって、合格点数基準を落としたいので、$kijyunにて合格点数を変えて行きたいのです。
上記のＳＱＬを入れても、旨く表示されないのです。

sql = select sum(tensu) as stensu, sum(tensu)-(select count(*) * $kijyun from testdb) as hensa from testdb where tensu >= $kijyun
このようにしたほうがいいですよね。

ご教授願います。