SQL sumのサンプルコード集 基本の小計出力から、NULL対策まで
  • facebookページ
  • twitterページ
  • 2019.09.05

    SQL sumのサンプルコード集 基本の小計出力から、NULL対策まで

    SQLで合計値を計算する関数sumの使い方についてまとめました。

    以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。

    sumの使い方

    sum(カラム名)で、対象カラムを集計します。

    group by とsumを組み合わせて小計を出す

    sum単体では単純な合計値しか出せませんが、group byと組み合わせることで分類ごとの小計を計算できます。

    上記は、部署ごとの年収の合計を小計として取得するSQLとなります。employees(社員テーブル)、dept_emp(社員・部署紐付けテーブル)、departments(部署テーブル)、salaries(年収テーブル)を結合し、group byで部署名を指定して部署ごとの小計を計算しています。

    salaries.to_dateに’9999-01-01’を指定しているのは、最新の年収のみを対象にするためです。

    SQLの実行結果は以下の通りです。

    SQL実行結果

    年収の合計では、Development(開発)、Production(製造)、Sales(営業)が大きくなっています。

    集約関数の使い方 countとgroup byでグループごとの総数を出す

    上記の結果では、社員一人ひとりの年収が高いのか、単に部署の人数が多いのか、年収合計が大きい理由がわかりません。

    さらにcountを組み合わせて、部署ごとの社員数を出力してみましょう。

    出力結果はこうなります。

    社員数総数は、部署ごとの社員番号(emp_no)のカウント数で求めました。

    せっかくなので年収平均も出力してみましょう。

    年収合計の高い開発部、製造部、営業部のうち、一人あたりの年収平均が高いのは営業部、ということがわかりました。

    同様に、MAX(最大値)、MIN(最小値)なども求められます。

    このように、group byと組み合わせて値を計算する関数を集約関数(または集計関数)と呼びます。

    集約関数はDBMSにより異なります。例えば、MySQLでは、STD(母標準偏差)、VAR_SAMP(標本分散)、GROUP_CONCAT(連結された文字列)などの関数が用意されています。

    関連)MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.19.1 GROUP BY (集約) 関数

    NULLを含むデータに、sumで合計を出すにはCOALESCE関数

    DBMSによっては、sumの集計対象カラムにNULLが含まれていると、sumによる合計値をNULLとして返すことがあります。

    COALESCE関数は、引数の中で最初に見つかったNULL以外の値を返す関数。

    例えば、COALESCE(salary,0)とすると、salariyカラムがNULLの場合に0を返します。

    下記のように記述することで、sumの対象カラムにNULLが含まれている場合でも、正しく合計値を計算することができます。

    なお、MySQLでは、NULLが含まれているカラムにsumを適用した場合でも、正しく合計値が計算できました。

    sumで年ごとの合計を出すには、group byにdate_format関数を指定

    年ごとの小計を出すには、以下のようにします。

    Sales(営業部)の誕生日の年ごとの年収合計を出力するSQLです。

    実行するとこうなります。

    SQL実行結果

    group by Date_format(birth_date, ‘%Y’)という指定で、年ごとの集計が可能。%Yの部分を変更することで、年月、付、曜日、時間、分、秒での集計が可能です。

    ただし、SQL Serverでは、date_formatという関数が存在しないため、CONVERT関数で代用する必要があります。(MySQL、Oracle、PostgreSQLではdate_formatを使用可能)

    sumの使い方まとめ

    ポテパンダの一言メモ
    • sumとgroup byを組み合わせると分類ごとの小計を出力可能
    • countやavgなどの集約関数もsumと同様に使える
    • sumの合計値がNULLになってしまう場合は、COALESCE関数を利用
    • 年ごと、月ごと、曜日ごとの集計にはgroup byにdate_format関数を組み合わせる

     


    ポテパンが提供するサービスについて

    本メディア「ポテパンスタイル」を運営する株式会社ポテパンは、エンジニアキャリア領域で複数サービスを提供しています。

    ポテパンフリーランス

    ポテパンフリーランス

    フリーランスエンジニアの方に高単価案件をご紹介しております。弊社ではフリーランス案件を常時300件ほど保有しており、その中からあなたに適した案件をご案内いたします。また、これから独立してフリーランスになる方の無料個別相談も承っております。フリーランスになった後の案件獲得方法やお金面(税金や保険など)についてお答えいたします!フリーエンジニアになりたい方向けのコンテンツも盛りだくさんです。

    ポテパンキャリア

    ポテパンキャリア

    エンジニア職専門の転職エージェントです。ポテパンキャリアでは、技術のわかるエージェントがあなたの転職をサポートします。エージェント自身がエンジニアなので、あなたと同じ目線で仕事内容や今後のキャリアについて一緒に考えることができます。年収800万円以上のハイスペック転職をご希望の方は「ポテパンプロフェッショナル」もご用意しておりますのでご利用下さいませ。

    ポテパンキャンプ

    ポテパンキャンプ

    ポテパンキャンプでは、RubyにてゼロからオリジナルのECサイトを作り上げてる3ヶ月間の実践型カリキュラムを提供しております。すでに本スクールの卒業生は、エンジニア職として様々な企業様に就職しております。なお、本スクールは受講料25万円と他社スクールに比べ格安となっており、またポテパンからご紹介させていただいた企業へ就職が決まった場合は、全額キャッシュバックいたします。



    株式会社ポテパンは、企業とエンジニアの最適なマッチングを追求しています。気になるサービスがあれば、ぜひ覗いてみてください!

    ポテクラバナー ポテプロバナー

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE

    ポテパンおすすめ案件