Re: aggregate function ? - Mailing list pgsql-novice

On 16/05/2007, at 03:43, Richard Broersma Jr wrote:

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

thanks


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

Yes, the rows selected from that table normally are between 100 and
4000 aprox.



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

yes, what I want really is the Sum(b.value_d) - sum(b.value_h)
accumulate

will be faster a function for this that this SELECT ... ?


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

GlobalGest=# SELECT A.oid, A.concepte, A.deure, A.haver
            (sum( B.deure )-sum(B.haver)) AS value_sum
       FROM assentaments AS A
INNER JOIN assentaments AS B
         ON A.oid <= B.oid
   WHERE A.numero=11189
   GROUP BY A.oid, A.concepte, A.deure, A.haver
   ORDER BY A.oid;
ERROR:  schema "a" does not exist

GlobalGest=#


thanks!


raimon