Обсуждение: 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

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

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

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

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