SQLの集計について

Question

テーブル１から担当ごとに収入と支出の合計とレコードの件数を
１つのＳＱＬで集計したいのですがうまく取得できません。

テーブル１
ID 担当１ 担当２ 収入 支出
１ Ａ Ｃ 100 400
２ Ａ Ｂ 200 550
３ Ａ Ａ 300 300
４ Ｂ Ａ 120 100
５ Ｂ Ｃ 250 200

期待する結果は以下の通りです。
担当 収入合計 支出合計 レコード件数
Ａ 720 1350 4
Ｂ 570 850 3
Ｃ 350 600 2

取得できるＳＱＬを教えていただけないでしょうか？
宜しくお願い致します。

Comments

[kutsulog]

担当者一覧みたいなテーブルはあったりしますか?

[taya]

はい。担当者のテーブルは存在します。
宜しくお願い致します。

Answer 1

id=3のレコードが担当者1と担当者2が重複しているけど、結果を見る限り二重に計算してはいけないらしいのが罠ですね。

とりあえずこんな感じにテーブル作ってあるとして、

select * from t;

+------+------+------+------+------+
| id | p1 | p2 | i | o |
+------+------+------+------+------+
| 1 | a | c | 100 | 400 |
| 2 | a | b | 200 | 550 |
| 3 | a | a | 300 | 300 |
| 4 | b | a | 120 | 100 |
| 5 | b | c | 250 | 200 |
+------+------+------+------+------+

こんな感じでどうでしょうか。

select
     p, sum(i), sum(o), count(*)
from (
    (select id,p1 as p,i,o from t)
    union
    (select id,p2 as p,i,o from t)
) as t1
group by p;

p1,p2が分かれていると扱いにくいのでp1だけとp2だけのテーブルに変形して異なっているカラム名をpに揃えてからunionで結合すれば、あとは普通にgroup byしてsumやcountを使用できます。またunionだと丁度いいことに全く同じ内容の行は一つだけにしてくれます。

実行結果

select p, sum(i), sum(o), count(*) from ((select id,p1 as p,i,o from t) union (select id,p2 as p,i,o from t)) as merged group by p;

+------+--------+--------+----------+
| p | sum(i) | sum(o) | count(*) |
+------+--------+--------+----------+
| a | 720 | 1350 | 4 |
| b | 570 | 850 | 3 |
| c | 350 | 600 | 2 |
+------+--------+--------+----------+

Comments

[taya]

対象の列が２つあるので悩んでおりました。
列名をそろえてunionする案、助かりました。
結果も期待通りです。ありがとうございました。

Answer 2

下記のSQLで行けると思いますがいかがでしょう?
※担当者1,担当者2のA,B,Cが担当者一覧テーブルの担当者IDを指している前提で書いています

SELECT
	  T2.担当者ID
	, SUM(CASE WHEN T1.担当者1 = T2.担当者ID OR T1.担当者2 = T2.担当者ID THEN T1.収入 ELSE 0 END) 収入
	, SUM(CASE WHEN T1.担当者1 = T2.担当者ID OR T1.担当者2 = T2.担当者ID THEN T1.支出 ELSE 0 END) 支出
	, SUM(CASE WHEN T1.担当者1 = T2.担当者ID OR T1.担当者2 = T2.担当者ID THEN 1 ELSE 0 END) 件数
FROM テーブル1 T1
JOIN 担当者テーブル T2
ON T2.担当者ID IN (T1.担当者1, T1.担当者2)
GROUP BY
	  T2.担当者ID

Comments

[taya]

回答ありがとうございます。おかげさまで解決できました。

Answer 3

正規化に問題がありそうなテーブルですね。
Oracle ですと、

SQL> WITH TABLE1 AS
  2      (
  3      SELECT '１' AS ID, 'Ａ' AS TANTO1, 'Ｃ' AS TANTO2, 100 AS SHUNYU, 400 AS SHISHUTSU FROM DUAL UNION ALL
  4      SELECT '２'      , 'Ａ'          , 'Ｂ'          , 200          , 550              FROM DUAL UNION ALL
  5      SELECT '３'      , 'Ａ'          , 'Ａ'          , 300          , 300              FROM DUAL UNION ALL
  6      SELECT '４'      , 'Ｂ'          , 'Ａ'          , 120          , 100              FROM DUAL UNION ALL
  7      SELECT '５'      , 'Ｂ'          , 'Ｃ'          , 250          , 200              FROM DUAL
  8      )
  9  SELECT A.TANTO
 10       , SUM(A.SHUNYU) AS SHUNYU_KEI
 11       , SUM(A.SHISHUTSU) AS SHISHUTSU_KEI
 12       , COUNT(*) AS KENSU
 13  FROM(
 14      SELECT T1.TANTO1 AS TANTO
 15           , T1.SHUNYU
 16           , T1.SHISHUTSU
 17      FROM TABLE1 T1
 18      UNION ALL
 19      SELECT T1.TANTO2
 20           , T1.SHUNYU
 21           , T1.SHISHUTSU
 22      FROM TABLE1 T1
 23      WHERE T1.TANTO1 <> T1.TANTO2  -- ここがおミソ
 24      ) A
 25  GROUP BY A.TANTO
 26  ORDER BY A.TANTO ;

TANTO     SHUNYU_KEI SHISHUTSU_KEI         KENSU
------ ------------- ------------- -------------
Ａ               720          1350             4
Ｂ               570           850             3
Ｃ               350           600             2