Create loop in postgresql

Started by Robert Buckleyover 13 years ago13 messagesgeneral

robertdbuckley@yahoo.com

over 13 years ago

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

salah jubeh

s_jubeh@yahoo.com

over 13 years ago

In reply to: Robert Buckley (#1)

Re: 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@yahoo.com>
To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Sent: Monday, September 3, 2012 5:30 PM
Subject: [GENERAL] Create loop in postgresql

Hi,

Here is the case statement.

End as z

from energie.tennet_auswertung_2010
;

If anyone can help I´d me grateful,

cheers,

Rob

Rainer Pruy

Rainer.Pruy@Acrys.COM

over 13 years ago

In reply to: salah jubeh (#2)

Re: Create loop in postgresql

And probably the op
would prefer to avoid recalculating the sum once for every row.....

select name,ges_kw_zgb, round(100*ges_kw_zgb/total.totalsum, 2) as z
from energie.tennet_auswertung_2010, (select sum(ges_kw_zgb) totalsum

From energie.tennet_auswertung_2010) as total

where ......

Regards
Rainer

Show quoted text

On 03.09.2012 17:45, salah jubeh wrote:

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@yahoo.com>
*To:* "pgsql-general@postgresql.org" <pgsql-general@postgresql.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

Robert Buckley

robertdbuckley@yahoo.com

over 13 years ago

In reply to: salah jubeh (#2)

Re: 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@yahoo.com>
An: Robert Buckley <robertdbuckley@yahoo.com>; "pgsql-general@postgresql.org" <pgsql-general@postgresql.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

Hi,

Here is the case statement.

End as z

from energie.tennet_auswertung_2010
;

If anyone can help I´d me grateful,

cheers,

Rob

Rainer Pruy

Rainer.Pruy@Acrys.COM

over 13 years ago

In reply to: Robert Buckley (#4)

Re: Create loop in postgresql

Try it with parentheses around the (SELECT....).

Show quoted text

On 03.09.2012 18:06, Robert Buckley wrote:

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@yahoo.com>
*An:* Robert Buckley <robertdbuckley@yahoo.com>;
"pgsql-general@postgresql.org" <pgsql-general@postgresql.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@yahoo.com>
*To:* "pgsql-general@postgresql.org" <pgsql-general@postgresql.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

Raymond O'Donnell

rod@iol.ie

over 13 years ago

In reply to: Robert Buckley (#4)

Re: Create loop in postgresql

On 03/09/2012 17:06, Robert Buckley wrote:

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

Yes, you don't need all those SELECTs. You probably want something like
this:

select
name, ges_kw_zgb,
round(100 * (ges_kw_zgb / sum(ges_kw_zgb)), 2)
from
energie.tennet_auswertung_2010
group by
name, ges_kw_zgb;

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

salah jubeh

s_jubeh@yahoo.com

over 13 years ago

In reply to: Robert Buckley (#4)

Re: 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@yahoo.com>
To: salah jubeh <s_jubeh@yahoo.com>; "pgsql-general@postgresql.org" <pgsql-general@postgresql.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...

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

Hi,

Here is the case statement.

End as z

from energie.tennet_auswertung_2010
;

If anyone can help I´d me grateful,

cheers,

Rob

Robert Buckley

robertdbuckley@yahoo.com

over 13 years ago

In reply to: salah jubeh (#7)

Re: Create loop in postgresql

Thanks....that worked a treat!

cheers,

Rob

________________________________
Von: salah jubeh <s_jubeh@yahoo.com>
An: Robert Buckley <robertdbuckley@yahoo.com>; "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Gesendet: 18:23 Montag, 3.September 2012
Betreff: Re: [GENERAL] Create loop in postgresql

Hello Robert,

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

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

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

Hi,

Here is the case statement.

End as z

from energie.tennet_auswertung_2010
;

If anyone can help I´d me grateful,

cheers,

Rob

Robert Buckley

robertdbuckley@yahoo.com

over 13 years ago

In reply to: salah jubeh (#7)

Re: 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@yahoo.com>
An: Robert Buckley <robertdbuckley@yahoo.com>; "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Gesendet: 18:23 Montag, 3.September 2012
Betreff: Re: [GENERAL] Create loop in postgresql

Hello Robert,

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

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

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

Hi,

Here is the case statement.

End as z

from energie.tennet_auswertung_2010
;

If anyone can help I´d me grateful,

cheers,

Rob

#10

Robert Buckley

robertdbuckley@yahoo.com

over 13 years ago

In reply to: Robert Buckley (#9)

Re: Create loop in postgresql

This works ok
SELECT round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2) FROM energie.tennet_auswertung_2010

but this does not

update energie.tennet_auswertung_2010 set "Test"=
(
SELECT round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2) FROM energie.tennet_auswertung_2010
);

?????????

________________________________
Von: Robert Buckley <robertdbuckley@yahoo.com>
An: salah jubeh <s_jubeh@yahoo.com>; "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Gesendet: 19:30 Montag, 3.September 2012
Betreff: 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!

How would I update the rows?

Cheers for any help,

Rob

________________________________
Von: salah jubeh <s_jubeh@yahoo.com>
An: Robert Buckley <robertdbuckley@yahoo.com>; "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Gesendet: 18:23 Montag, 3.September 2012
Betreff: Re: [GENERAL] Create loop in postgresql

Hello Robert,

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

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

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

Hi,

Here is the case statement.

End as z

from energie.tennet_auswertung_2010
;

If anyone can help I´d me grateful,

cheers,

Rob

#11

Raymond O'Donnell

rod@iol.ie

over 13 years ago

In reply to: Robert Buckley (#9)

Re: Create loop in postgresql

On 03/09/2012 18:30, Robert Buckley wrote:

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

The answer's in the error message - the subquery is returning more than
one row. If you're using a subquery as an expression to be used to
update the column, then it can only return one row - otherwise which row
should be used for the update?

Try running the subquery on its own, and play with it until you get a
single value.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

#12

Raymond O'Donnell

rod@iol.ie

over 13 years ago

In reply to: Robert Buckley (#10)

Re: Create loop in postgresql

On 03/09/2012 18:42, Robert Buckley wrote:

This works ok
SELECT round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From
energie.tennet_auswertung_2010)),2) FROM energie.tennet_auswertung_2010

but this does not

update energie.tennet_auswertung_2010 set "Test"=
(
SELECT round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From
energie.tennet_auswertung_2010)),2) FROM energie.tennet_auswertung_2010
);

?????????

Define "works". What do you expect to get, and what so you actually get?
We can't read your mind. :-)

Also, please don't top-post - thanks.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

#13

salah jubeh

s_jubeh@yahoo.com

over 13 years ago

In reply to: Robert Buckley (#9)

Re: Create loop in postgresql

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@yahoo.com>
To: salah jubeh <s_jubeh@yahoo.com>; "pgsql-general@postgresql.org" <pgsql-general@postgresql.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!

How would I update the rows?

Cheers for any help,

Rob

________________________________
Von: salah jubeh <s_jubeh@yahoo.com>
An: Robert Buckley <robertdbuckley@yahoo.com>; "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Gesendet: 18:23 Montag, 3.September 2012
Betreff: Re: [GENERAL] Create loop in postgresql

Hello Robert,

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

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

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

Hi,

Here is the case statement.

End as z

from energie.tennet_auswertung_2010
;

If anyone can help I´d me grateful,

cheers,

Rob