Skip site navigation (1) Skip section navigation (2)

Re: aggregate function ?

From: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
To: Raimon Fernandez <coder(at)montx(dot)com>
Cc: Novice Postgresql-list <pgsql-novice(at)postgresql(dot)org>
Subject: Re: aggregate function ?
Date: 2007-05-16 01:43:15
Message-ID: 605953.20165.qm@web31809.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
> Any recomended good book for SQL ?

http://www.elsevier.com/wps/find/bookdescription.cws_home/706077/description#description
http://www.elsevier.com/wps/find/bookdescription.cws_home/710075/description#description

you can buy these books almost any where.  However, I can only find the 2nd addition for the SQL
puzzles book on this website.  I recommend the 2nd addition of the first.


> Yes, that for getting the accumulate of line 2 (50) first I  have to  
> know the accumulate of line 1 (75)
> 
> Maybe with this example is more clear ...
> I changed the fields from mines, but as this table has more than  
> 700.000 rows, I would like to put a LIMIT or WHERE clausule, but it  
> doesn't work ....

Well the number of rows will probably be a problem, since the running total ( B ) is going to have
to scan most of the table for each row returned from your table ( A ).  However, you can easily
limit the rows returned by table ( A ):
 
    SELECT A.oid, A.detail, A.value_d, A.value_h
            sum( B.value_d - B.value_h) AS value_sum
      FROM Assentaments AS A
INNER JOIN Assentaments AS B
        ON A.oid <= B.oid
     WHERE A.oid BETWEEN 1 AND 100 -- you will have to pick the appropriate values
  GROUP BY A.oid, A.detail, A.value_d, A.value_h
  ORDER BY A.oid;

If ... sum( B.value_d - B.value_h) AS value_sum ... is not what you 
really want, we can force your original syntax, but we will have to
reform you query a little.  Also, getting it to work will probably
hurt performance a bit more.


 
> With this code it says: Error, Shcema 'a' doesn't exist ...

I am not sure about this error.  It doesn't make sense to me.  Could you Copy/Paste the actual
query with the associated error message?

Regards,
Richard Broersma Jr.

In response to

Responses

pgsql-novice by date

Next:From: Raimon FernandezDate: 2007-05-16 07:56:27
Subject: Re: aggregate function ?
Previous:From: Raimon FernandezDate: 2007-05-16 00:42:13
Subject: Re: aggregate function ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group