Hi,
I need some help in sql - postgresql:
I create this 4 tables:
CREATE TABLE a (
a_id SERIAL PRIMARY KEY,
a_name text );
CREATE TABLE b (
b_id SERIAL PRIMARY KEY,
b_price INT2 );
CREATE TABLE c (
c_id SERIAL PRIMARY KEY,
a_id INT4 REFERENCES a ON UPDATE CASCADE ON DELETE CASCADE,
b_id INT4 REFERENCES b ON UPDATE CASCADE ON DELETE CASCADE,
c_price INT2 );
CREATE TABLE d (
d_id SERIAL PRIMARY KEY,
a_id INT4 REFERENCES a ON UPDATE CASCADE ON DELETE CASCADE,
b_id INT4 REFERENCES b ON UPDATE CASCADE ON DELETE CASCADE,
d_price INT2 );
Insert some values:
INSERT INTO a (a_name) VALUES ('org1');
INSERT INTO a (a_name) VALUES ('org2');
INSERT INTO a (a_name) VALUES ('org3');
INSERT INTO b (b_price) VALUES (100);
INSERT INTO b (b_price) VALUES (200);
INSERT INTO b (b_price) VALUES (50);
INSERT INTO c (a_id, b_id,c_price) VALUES (1,1,50);
INSERT INTO c (a_id, b_id,c_price) VALUES (2,1,50);
INSERT INTO c (a_id, b_id,c_price) VALUES (1,2,100);
INSERT INTO c (a_id, b_id,c_price) VALUES (2,2,100);
INSERT INTO c (a_id, b_id,c_price) VALUES (1,3,25);
INSERT INTO c (a_id, b_id,c_price) VALUES (3,3,25);
INSERT INTO d (a_id, b_id,d_price) VALUES (1,1,50);
INSERT INTO d (a_id, b_id,d_price) VALUES (2,1,50);
INSERT INTO d (a_id, b_id,d_price) VALUES (1,2,100);
INSERT INTO d (a_id, b_id,d_price) VALUES (2,2,100);
a_id | a_name
--+
1 | org1
2 | org2
3 | org3
b_id | b_price
--+-
1 | 100
2 | 200
3 | 50
c_id | a_id | b_id | c_price
--+--+--+-
1 |1 |1 | 50
2 |2 |1 | 50
3 |1 |2 | 100
4 |2 |2 | 100
5 |1 |3 | 25
6 |3 |3 | 25
d_id | a_id | b_id | d_price
--+--+--+-
1 |1 |1 | 50
2 |2 |1 | 50
3 |1 |2 | 100
4 |2 |2 | 100
SELECT SUM(c_price) as sum,(SELECT SUM(d_price) FROM d WHERE
a_id=t1.a_id ) AS payed, SUM(c_price)-(SELECT SUM(d_price) FROM d WHERE
a_id=t1.a_id ) AS to_pay FROM c AS t1 group by a_id order by payed;
the result of this query is:
sum | payed | to_pay
-+---+
150 | 150 | 0
175 | 150 | 25
25 | |
*The question is* : how can I force that the result of the col payed to
be zerro 0 insted of nothing (NULL)
and the order will be in way that the zerro's values comes first.
and the result will be:
sum | payed | to_pay
-+---+
25 | 0 | 0
150 | 150 | 0
175 | 150 | 25
Thanks
Suad
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
