i got closer but i can't figure out this:
individually:
A) select a.job, sum(b.money) from t1 as a left join t2 as b on
a.account = b.account where a.job = "ca1" and b.money > 0;
+----------+---------------------------+
| job | sum(b.money) |
+----------+---------------------------+
| ca1 | 1464 |
+----------+---------------------------+
B) select a.job, sum(b.money) from t1 as a left join t2 as b on
a.account = b.account where a.job = "ca1" and b.money < 0;
+----------+---------------------------+
| job | sum(b.money) |
+----------+---------------------------+
| ca1 | -129 |
+----------+---------------------------+
BUT if i put both together:
select a.job, sum(b.money), sum(c.money) from t1 as a left join t2 as b
on a.account = b.account left join t2 as c on a.account = c.account
where a.job = "ca1"and c.money < 0 and b.money > 0;
i get:
+----------+---------------------------+---------------------------+
| job | sum(b.money) | sum(c.money) |
+----------+---------------------------+---------------------------+
| ca1 | 180 |
-129 |
+----------+---------------------------+---------------------------+
it seems the condition c.money < 0 is respected but the b.money > 0 is
acting weired... i changed the order but that's not it. and the 1464 is
the correct number for b.money...
any ideas?!.....
thanks.....
kalin m wrote:
hi...
how do i do conditional sums? like:
select a.job, sum(if b.amount > 0 then amount end if ) from t1 as a
left join t2 as b on a.account=b.accoun where a.account = b.account
group by a.job;
or
select a.job, if b.amount > 0 then sum(b.amount) end if from t1 as
a left join t2 as b on a.account=b.accoun where a.account = b.account
group by a.job;
or something like it. it's be awesome if it can be done...
thanks...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
