Webサイト制作コースのお申し込みはこちら Webサイト制作コースのお申し込みはこちら

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

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

sumの使い方

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

SELECT sum(salary) FROM `salaries`

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

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

select departments.dept_name, sum(salary) from employees
inner join salaries on salaries.emp_no = employees.emp_no
inner join dept_emp on employees.emp_no = dept_emp.emp_no
inner join departments on dept_emp.dept_no = departments.dept_no
where salaries.to_date = '9999-01-01'
group by dept_name

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

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

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

SQL実行結果

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

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

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

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

select departments.dept_name, 
       Count(employees.emp_no), 
       Sum(salary) 
from   employees 
       inner join salaries 
               on salaries.emp_no = employees.emp_no 
       inner join dept_emp 
               on employees.emp_no = dept_emp.emp_no 
       inner join departments 
               on dept_emp.dept_no = departments.dept_no 
where  salaries.to_date = '9999-01-01' 
group  by dept_name

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

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

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

select departments.dept_name, 
       Count(employees.emp_no), 
       Avg(salary), 
       Sum(salary) 
from   employees 
       inner join salaries 
               on salaries.emp_no = employees.emp_no 
       inner join dept_emp 
               on employees.emp_no = dept_emp.emp_no 
       inner join departments 
               on dept_emp.dept_no = departments.dept_no 
where  salaries.to_date = '9999-01-01' 
group  by dept_name

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

同様に、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が含まれている場合でも、正しく合計値を計算することができます。

SELECT sum(COALESCE(salary,0)) FROM `salaries`

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

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

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

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

select Date_format(birth_date, '%Y') as year, 
       Sum(salary) 
from   employees 
       inner join salaries 
               on salaries.emp_no = employees.emp_no 
       inner join dept_emp 
               on employees.emp_no = dept_emp.emp_no 
       inner join departments 
               on dept_emp.dept_no = departments.dept_no 
where  salaries.to_date = '9999-01-01' 
       and dept_name = 'Sales' 
group  by Date_format(birth_date, '%Y')

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

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関数を組み合わせる

 

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか？

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。