マイナスの数字だけを算出したい

Question

今、
●テーブル

t1(テーブル1)
会員ID, 日付（年・月・日・時刻）, 金額

t2(テーブル2)
会員ID, 残量

という２つのテーブルがあります。
テーブル1とテーブル2の会員IDはユーザーごとに一致しています。
テーブル2の残量はユーザーが購入した時は正の数で記載（ex.50）
ユーザーが消費した時は負の数で記載（ex.-30）されます。
テーブル1の金額が５０００以上のユーザーの、テーブル2の負の数で記載された値の残量の合計を求めたいです。

SELECT 会員ID, 残量, 消費量合計
FROM( SELECT 会員ID, 金額 FROM t1
UNION ALL
SELECT 会員ID, 残量 FROM t2
) 
GROUP BY 会員ID, 残量;

のように書いたのですが、このSQL内で負の数のみの残量を求める方法と金額 が５０００以上のソートの仕方がわからず....
どう書けばいいのでしょうか？

Comments

[SVC34]

カラム名とその使い方に違和感を感じます。カラム名からはt1が購入・消費履歴（正の値が購入、負の値が消費を表す）のトランザクションで、t2が残量を管理するマスタ（通常は負の値にならない）と想定されます。一方質問の文章では取り扱いが逆になっていますが、本当にそのような設計なのでしょうか。

Answer 1

SELECT t1.会員ID, t1.金額, SUM(t2.残量) AS 残量
FROM t1 INNER JOIN t2 ON (t2.会員ID = t1.会員ID AND t2.残量<0)
WHERE t1.金額 >= 5000
GROUP BY t1.会員ID, t1.金額;

ですかね。t2 を join するときに、残量が負のものだけ選択して join してやれば、あとは普通に SUM するだけです。

Answer 2

t1(テーブル1) はユーザで１個と想定して

select
 t1.kaiin 会員ID,
 sum(zan) 残量,
 sum(case when zan<0 then zan else 0 end) 消費量合計
 from tbl01 t1 join tbl02 t2 
     on t1.kaiin=t2.kaiin and kingaku>=5000 
group by 1 order by 1;


-- table define
create table tbl01 (kaiin int, hiduke date, kingaku int);
create table tbl02 (kaiin int, zan int);