hi
I have a problem whith inforcing the result of a selekect query, here is
my tables and the query:
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 FROM c AS t1 group by a_id order by payed;
the result of this query is:
sum | payed
-----+-------
150 | 150
175 | 150
25 |
(3 rows)
The question is : how can I force that the result of the col payed to be
zerro "0" insted of notthig (NULL)
and the order will be in way that the zerro's values comes first.
and the result will be:
sum | payed
-----+-------
25 | 0
150 | 150
175 | 150
Thanks
Suad
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
