none
GROUP BY での引き算 合計 RRS feed

  • 質問

  • はじめまして質問いたします。

    SUM関数を使ってテーブル作っています。

    select name,sum(nyuko-syukko) as kei

    from   kanritable

    group by      name

    以上のようなコードですが結果としてtable1から入力されても数字が反映されません。ちなみにtable1のデザインは

    id           1

    name      apple

    nyuko     100

    syukko      20

    となり nyuko     100  と入れてもその度ごとに   syukko    20 を入れないと数字が反映されません。

    20を入れなくても反映される方法はないでしょうか。お教え願います。

     

    2011年8月17日 5:13

回答

  • データベースの NULL 値への対処ができていないということですね。

    算術に NULL が含まれる場合、その結果も NULL になります。
    sum(nyuko-syukko) とした場合、各行について次の計算が行われ、各結果に対して sum が実行されます。

    1行目:100 - NULL = NULL
    2行目:200 - NULL = NULL
    3行目:NULL - 40 = NULL

    sum は NULL の行を無視して合計してくれますが、この例の場合は結局 NULL になっちゃいますね。

    対処ですが、SQL 文を以下のように isnull 関数を使ったものに変更してみてください。
    こうすれば NULL 値をゼロに置き換えて処理できます。

    select name, sum(isnull(nyuko, 0) - isnull(syukko, 0)) as kei
    from kanritable
    group by name

    もしくは

    select name, isnull(sum(nyuko), 0) - isnull(sum(syukko), 0) as kei
    from kanritable
    group by name

    別の対処としては、入力されなかった項目にはゼロを格納するようにしてもいいかもしれませんね。


    • 編集済み TH01 2011年8月18日 1:51 1行目の計算式のミスを訂正
    • 回答としてマーク 山本春海 2011年8月30日 8:56
    2011年8月17日 11:06

すべての返信

  • 「SUM関数を使ってテーブル作っています」とありますが、テーブルの作成は CREATE TABLE 文です。

    「kanritable」と「table1」の関係はなんでしょう?

    「syukko 20 を入れないと数字が反映されません」どこに20を入れたのか、説明があいまいで質問内容そのものがわかりません。もう一度、

    • 前提として何があり
    • 何をしようとしていて
    • どう自動化されることを期待していて
    • 現実には何を手動で入力しているのか

    をご自身で理解・整理しましょう。

    2011年8月17日 5:33
  • 失礼しました、。kanritableというテーブルを作りました.デザインは

    id            char(10)

    name      char(20)

    nyuko      int

    syukko     int

    です。

    入力のためのフォーム form1をVC#で作りkanritableのid以下....をtextboxに四個貼り付けました。そして

    id           1

    name      apple

    nyuko     100

    syukko      20

    と値を入力しました。次にview1を作りました。SQLコーどは下記の通りです。

    select name,sum(nyuko-syukko) as kei

    from   kanritable

    group by      name

    form2にview1を貼り付け一覧表を貼り付けました。まずnyuko 100,syukko 20と入力し、次にnyuko 120,syukko 30両方入力しないとview1の一覧表に正しい計算結果 80、70が反映されないのです。ということはnyuko 100、nyuko 200とnyukoを2回続けると正しい計算結果が表示されません。nyuko 100 syukko 入力無し 、nyuko 200 syukko 入力無し、nyuko 入力無し syukko 40と順にform1から3回入力しても kei が260と正しく表示されるようにしたく考えています。よろしくお願いします。

    2011年8月17日 9:16
  • データベースの NULL 値への対処ができていないということですね。

    算術に NULL が含まれる場合、その結果も NULL になります。
    sum(nyuko-syukko) とした場合、各行について次の計算が行われ、各結果に対して sum が実行されます。

    1行目:100 - NULL = NULL
    2行目:200 - NULL = NULL
    3行目:NULL - 40 = NULL

    sum は NULL の行を無視して合計してくれますが、この例の場合は結局 NULL になっちゃいますね。

    対処ですが、SQL 文を以下のように isnull 関数を使ったものに変更してみてください。
    こうすれば NULL 値をゼロに置き換えて処理できます。

    select name, sum(isnull(nyuko, 0) - isnull(syukko, 0)) as kei
    from kanritable
    group by name

    もしくは

    select name, isnull(sum(nyuko), 0) - isnull(sum(syukko), 0) as kei
    from kanritable
    group by name

    別の対処としては、入力されなかった項目にはゼロを格納するようにしてもいいかもしれませんね。


    • 編集済み TH01 2011年8月18日 1:51 1行目の計算式のミスを訂正
    • 回答としてマーク 山本春海 2011年8月30日 8:56
    2011年8月17日 11:06
  • 何の値が正しいのでしょうかね。

    syukkoが入力されていないとき、それは NULL であるべきなのか、 0 とみなして計算してよいものか。 0 とみなしていいのであれば、クエリーでISNULLを使うのでなく、 syukko カラムを int NOT NULL DEFAULT 0 にすべきでしょう。

    もっと問題なのは、nyukoが入力されずにsyukkoだけの場合、3行目は 0 で計算されたいのか -40 にしたいのか。

    こういった部分が不明確なまま、SQL Serverにほれほれ計算せよ、と命令しても計算できるわけがありませんよね。

    2011年8月18日 1:10
  • TH01さんぴったりはまりました。ありがとうございました。

    これで一気に進めます。感謝です。

    2011年8月18日 8:28