On Sat, 13 Sep 2003, Łukasz Jarosław Mozer wrote:

...

SELECT users.id AS id, SUM((type * 2 - 7) * value) AS balance FROM users LEFT JOIN cash ON users.id = cash.userid AND (cash.type = 3 OR cash.type = 4) GROUP BY users.id

dzieki Ci wielkie za to.... gdybys tylko napisal mi gdzie mam dodac *where users.status = 3* bo ni w 5.. ni w 10.. mi nie pasuje....

SELECT users.id AS id, SUM((type * 2 - 7) * value) AS balance

FROM users LEFT JOIN cash ON users.id = cash.userid AND (cash.type

= 3 OR cash.type = 4) AND users.status = 3 GROUP BY users.id ^^^^^^^^^^^^^^^^^^^^

Dla dociekliwych podaję zapytanie, o które chodziło na początku wątku, czyli zwracające dłużników (niestety zadziała pewnie tylko na postgresie):

select name, lastname, ((select SUM(value) from cash t1 where type = 3 and t1.userid= t3.userid group by userid, type) - (select SUM(value) from cash t2 where type=4 and t2.userid = t3.userid group by userid, type)) as bilans from cash t3 LEFT JOIN users on userid=users.id where ((select SUM(value) from cash t1 where type = 3 and t1.userid= t3.userid group by userid, type) - (select SUM(value) from cash t2 where type=4 and t2.userid = t3.userid group by userid, type)) < 0 group by userid, name, lastname order by bilans

-- Pozdrawiam Aleksander Machniak ( A.L.E.C ) http://k27.prv.pl/alec GG-2275252