From: | salah jubeh <s_jubeh(at)yahoo(dot)com> |
---|---|
To: | Robert Buckley <robertdbuckley(at)yahoo(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Create loop in postgresql |
Date: | 2012-09-03 18:42:35 |
Message-ID: | 1346697755.45596.YahooMailNeo@web122201.mail.ne1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello Rob,
there are many ways to do this task. This way is not optimal but any way mimic the example below and you will get it
CREATE TABLE test_update
(
id integer NOT NULL,
"value" numeric,
percentage numeric,
CONSTRAINT test_update_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
insert into test_update (id, value) values (1,5), (2, 10);
update test_update as b
set percentage = (Select a.value/(select sum(value) from test_update) from test_update as a where a.id = b.id)
________________________________
From: Robert Buckley <robertdbuckley(at)yahoo(dot)com>
To: salah jubeh <s_jubeh(at)yahoo(dot)com>; "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Sent: Monday, September 3, 2012 7:30 PM
Subject: Re: [GENERAL] Create loop in postgresql
Now even stranger is that I can´t update a column with the query.
Update energie.tennet_auswertung_2010 set "Test"=(
SELECT round(100*ges_kw_zgb/total.totalsum, 2) from energie.tennet_auswertung_2010,
(select sum(ges_kw_zgb) totalsum From energie.tennet_auswertung_2010) as total
);
ERROR: more than one row returned by a subquery used as an expression
But If I use Insert as below it DOES work!
insert into energie.tennet_auswertung_2010("Test") SELECT round(100*ges_kw_zgb/total.totalsum, 2)
from energie.tennet_auswertung_2010, (select sum(ges_kw_zgb) totalsum From energie.tennet_auswertung_2010) as total;
How would I update the rows?
Cheers for any help,
Rob
________________________________
Von: salah jubeh <s_jubeh(at)yahoo(dot)com>
An: Robert Buckley <robertdbuckley(at)yahoo(dot)com>; "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Gesendet: 18:23 Montag, 3.September 2012
Betreff: Re: [GENERAL] Create loop in postgresql
Hello Robert,
I just gave an example and this also can be optimized . but let me first clarify one thing here.
since you have the same behaviour for all values , there is no need to use case in the first place. So just drop it.
I think below would be the correct syntax
select name,ges_kw_zgb, (SELECT round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2)) FROM
energie.tennet_auswertung_2010;
Regards
________________________________
From: Robert Buckley <robertdbuckley(at)yahoo(dot)com>
To: salah jubeh <s_jubeh(at)yahoo(dot)com>; "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Sent: Monday, September 3, 2012 6:06 PM
Subject: Re: [GENERAL] Create loop in postgresql
this give an error.
select name,ges_kw_zgb, SELECT round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2);
ERROR: syntax error at or near "SELECT"
LINE 2: select name,ges_kw_zgb, SELECT round(100 * (ges_kw_zgb / (se...
________________________________
Von: salah jubeh <s_jubeh(at)yahoo(dot)com>
An: Robert Buckley <robertdbuckley(at)yahoo(dot)com>; "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Gesendet: 17:45 Montag, 3.September 2012
Betreff: Re: [GENERAL] Create loop in postgresql
I am wondering why do not you write it like this
select name,ges_kw_zgb, select round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2) ......
Regards
________________________________
From: Robert Buckley <robertdbuckley(at)yahoo(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Sent: Monday, September 3, 2012 5:30 PM
Subject: [GENERAL] Create loop in postgresql
Hi,
I am trying to loop through the records in a table and update a column. I can do this with a case statement but I would like to simplify this to a simple loop statement. I can´t seem to work out how to do it though.
Here is the case statement.
select name,ges_kw_zgb,
case
When name='Bad Harzburg' then (select round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2))
When name='Braunlage' then (select round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2))
When name='Braunschweig' then (select round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2))
When name='Büddenstedt' then (select round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2))
End as z
from energie.tennet_auswertung_2010
;
Instead of having to write the name variable in the function, I would just like to iterate through each record and execute the select round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2) command.
If anyone can help I´d me grateful,
cheers,
Rob
From | Date | Subject | |
---|---|---|---|
Next Message | Geert Mak | 2012-09-03 22:38:10 | Re: "Too far out of the mainstream" |
Previous Message | Raymond O'Donnell | 2012-09-03 18:29:33 | Re: Create loop in postgresql |