Re: help in sql - postgresql

suad wrote:
> Hi,
>
> I need some help in sql - postgresql:



Yay a postgres question! :D hee hee

> *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

COALESCE will do it for you:

SELECT SUM(c_price) as sum,(SELECT COALESCE(SUM(d_price), 0) FROM d
WHERE a_id=t1.a_id ) AS payed, SUM(c_price)-(SELECT
COALESCE(SUM(d_price), 0) FROM d WHERE a_id=t1.a_id ) AS to_pay FROM c
AS t1 group by a_id order by payed;

sum | payed | to_pay
-----+-------+--------
25 | 0 | 25
150 | 150 | 0
175 | 150 | 25
(3 rows)

http://www.postgresql.org/docs/8.1/interactive/functions-con ditional.html


--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

help in sql - postgresql

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

Re: help in sql - postgresql

Hi,
Thanks a lot,
That exactly wat I need


:)
Suad


Chris wrote:

> suad wrote:
>
>> Hi,
>>
>> I need some help in sql - postgresql:
>
>
>
>
> Yay a postgres question! :D hee hee
>
>> *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
>
>
> COALESCE will do it for you:
>
> SELECT SUM(c_price) as sum,(SELECT COALESCE(SUM(d_price), 0) FROM d
> WHERE a_id=t1.a_id ) AS payed, SUM(c_price)-(SELECT
> COALESCE(SUM(d_price), 0) FROM d WHERE a_id=t1.a_id ) AS to_pay FROM c
> AS t1 group by a_id order by payed;
>
> sum | payed | to_pay
> -----+-------+--------
> 25 | 0 | 25
> 150 | 150 | 0
> 175 | 150 | 25
> (3 rows)
>
> http://www.postgresql.org/docs/8.1/interactive/functions-con ditional.html
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php