SQL文で４つのテーブルから参照して、カラムの合計値を重複せずに出したい

Question

行いたいこと

現在、4つのテーブルを参照してその中のカラムの合計値を出したいのですが、明らかに値が大きくなってしまいます。
調べたところ、inner joinを行うとテーブルのレコード数が重複してしまい、重複した値もsumに取り入れられてしまいます。

現状、inner joinの一つを from句の中に入れて1つのテーブルからの重複は免れたのですが、残り2つのテーブルが重複してしまいます。
回答わかる方いらっしゃいますでしょうか？

テーブルとカラム

• usersテーブル

  id	name	...
  1	test	...
  2	aaaa	...
  3	bbbb	...
  ...

• appsテーブル

  id	user_id	name	...
  1	2	app_test	...
  2	2	cccc	...
  3	2	dddd	...
  ...

• ▲▲テーブル

  id	app_id	...
  1	3	...
  2	1	...
  3	2	...
  ...

• ××テーブル

  id	app_id	...
  1	2	...
  2	1	...
  3	3	...
  ...

• 出したい値
  → 同じuserが作成したappsの▲▲テーブルと××テーブルに紐づいた合計
  ex:
  aaaa(user_id:2)が作成したapp(ここでは、app_id:[1,2,3])に紐づく▲▲テーブルと××テーブルのカラム集計(sum)

試したこと

SQL文(from句内にselect文を作成)

select (
sum(▲▲.カラム名1), 
sum(▲▲.カラム名2), 
sum(××.カラム名), 
count(apps.id) 
)
from (select id, user_id, count(apps.id) from apps where user_id = 2) as apps
inner join inner join users on apps.user_id = users.id
inner join ▲▲ on apps.id = ▲▲.app_id 
inner join ×× on apps.id = ××.app_id;

sumの中が期待される値よりも大きくなってしまう。

SQL文(カラムごとにdistinct生成)

select (
sum(distinct ▲▲.カラム名1), 
sum(distinct ▲▲.カラム名2), 
sum(distinct ××.カラム名), 
count(apps.id) 
)
from (select id, user_id, count(apps.id) from apps where user_id = 2) as apps
inner join inner join users on apps.user_id = users.id
inner join ▲▲ on apps.id = ▲▲.app_id 
inner join ×× on apps.id = ××.app_id;

sumの中が期待される値よりも小さくなってしまう。

Answer 1

全部をINNER JOINすると▲▲と××かけ合わせたレコードが出てくるので、大きくなると思いますし、
DISTINCTは▲▲と××でそれぞれのテーブル内で重複をカットするので意味が違います。

いくつか解決策はありますが、こういうときは段階を追って実現すると良いと思います。

まずは▲▲テーブルでユーザごとの合計を出してみます。
※求めたいカラム名を便宜的にXとYとしました。

SELECT apps.user_id, SUM(▲▲.X), SUM(▲▲.Y) 
FROM apps 
INNER JOIN ▲▲ ON apps.id = ▲▲.id
GROUP BY user_id;

これでユーザIDごとの合計値が出ると思います。
××テーブルでも同様にします。

SELECT apps.user_id, SUM(××.Z) 
FROM apps 
INNER JOIN ×× ON apps.id = ××.id
GROUP BY user_id;

上の２つを副問合せでsum1、sum2にして、usersテーブルと結合すれば求めたい情報になるかと思います。

SELECT users.id, users.name, sum1.x, sum1.y, sum2.z
FROM users
INNER JOIN
(
  SELECT apps.user_id, SUM(▲▲.X) as x, SUM(▲▲.Y) as y 
  FROM apps 
  INNER JOIN ▲▲ ON apps.id = ▲▲.id
  GROUP BY user_id
) as sum1
ON users.id = sum1.user_id
INNER JOIN
(
  SELECT apps.user_id, SUM(××.Z) as z
  FROM apps 
  INNER JOIN ×× ON apps.id = ××.id
  GROUP BY user_id
) as sum2
ON users.id = sum2.user_id
ORDER BY users.id;

Comments

[THitokuse]

INNER JOINの中でselect文作成できることlは知らなかったので勉強になりました。
そちらの方法で期待していた値が取れました。
ありがとうございます！！

Answer 2

appsのidを限定した集計にするという事が目的の場合の原因は、apps のuser_id毎に取得するIDが１件になっていない事です。
下記はMax()を取得しています。

select user_id, max(id) as id, count(id) as cnt 
from apps 
where user_id = 2

上記のように、IDを限定した上で結合すれば求める結果になるかと思います。

また、user_id毎にappsに関連したデータの集計という場合は、以下の様になります。

select  user_id
      , sum(▲▲.カラム名1)
      , sum(▲▲.カラム名2)
      , sum(××.カラム名)
      , count(distinct apps.id)
from  apps 
      inner join ▲▲ 
      on apps.id = ▲▲.app_id 
      inner join ×× 
      on apps.id = ××.app_id
where apps.user_id = 2
group by apps.user_id

Comments

[THitokuse]

回答ありがとうございます！
IDを限定して出力した後さらにINNER JOIN内でselect文を回す必要があったみたいです！
解決しました！ありがとうございます！