Thread: Sum

Sum

From
Marcelo Pereira
Date:
Hello All,

I have a single table as:

cod     date       value
---+-------------+-------
1  | 2002-03-12  |  5
2  | 2002-03-13  |  4
3  | 2002-03-13  |  7
4  | 2002-03-14  |  3

... and I would like to do a select that do `partial sums'.

So, for instance, I would like to do a select that returns:

cod     date       value   partial sum
---+-------------+-------+--------------
1  | 2002-03-12  |   5   |      5
2  | 2002-03-13  |   4   |      9
3  | 2002-03-13  |   7   |     16
4  | 2002-03-14  |   3   |     19

As you can see, the row `partial sum' is what I am looking for. Do you
have any idea how can I do it?

Thanks in advance,

Marcelo Pereira

-- Remember that only God and Esc+:w saves.
        __
       (_.\           Marcelo Pereira       |
        / / ___       marcelo@pereira.com   |
       / (_/ _ \__    [Math|99] - IMECC     |
_______\____/_\___)___Unicamp_______________/



Re: Sum

From
Philip Hallstrom
Date:
I don't know of a SQL query that would do it, but you could do it in pgsql
or some other language... just run the normal query (the first one) and
then as you loop through the result set add 'value' to 'partial_sum' and
there you have it...

-philip

On Thu, 28 Mar 2002, Marcelo Pereira wrote:

> Hello All,
>
> I have a single table as:
>
> cod     date       value
> ---+-------------+-------
> 1  | 2002-03-12  |  5
> 2  | 2002-03-13  |  4
> 3  | 2002-03-13  |  7
> 4  | 2002-03-14  |  3
>
> ... and I would like to do a select that do `partial sums'.
>
> So, for instance, I would like to do a select that returns:
>
> cod     date       value   partial sum
> ---+-------------+-------+--------------
> 1  | 2002-03-12  |   5   |      5
> 2  | 2002-03-13  |   4   |      9
> 3  | 2002-03-13  |   7   |     16
> 4  | 2002-03-14  |   3   |     19
>
> As you can see, the row `partial sum' is what I am looking for. Do you
> have any idea how can I do it?
>
> Thanks in advance,
>
> Marcelo Pereira
>
> -- Remember that only God and Esc+:w saves.
>         __
>        (_.\           Marcelo Pereira       |
>         / / ___       marcelo@pereira.com   |
>        / (_/ _ \__    [Math|99] - IMECC     |
> _______\____/_\___)___Unicamp_______________/
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>


Re: Sum

From
Marcelo Pereira
Date:
Hello Philip,

I am using PHP to show the results in a html file, and I have this
`partial sum' running the same way you told. I am just looking around to
know if I can do this straight from a sql query, without any type of
external script, as I am already doing.

Thanks for you answer.

Best Regards,

Marcelo Pereira

-- Remember that only God and Esc+:w saves.
        __
       (_.\           Marcelo Pereira       |
        / / ___                             |
       / (_/ _ \__    Matematica/99 - IMECC |
_______\____/_\___)___Unicamp_______________/

--- Philip Hallstrom, with his fast fingers, wrote:

:> I don't know of a SQL query that would do it, but you could do it in pgsql
:> or some other language... just run the normal query (the first one) and
:> then as you loop through the result set add 'value' to 'partial_sum' and
:> there you have it...
:>
:> -philip
:>
:> On Thu, 28 Mar 2002, Marcelo Pereira wrote:
:>
:> > Hello All,
:> >
:> > I have a single table as:
:> >
:> > cod     date       value
:> > ---+-------------+-------
:> > 1  | 2002-03-12  |  5
:> > 2  | 2002-03-13  |  4
:> > 3  | 2002-03-13  |  7
:> > 4  | 2002-03-14  |  3
:> >
:> > ... and I would like to do a select that do `partial sums'.
:> >
:> > So, for instance, I would like to do a select that returns:
:> >
:> > cod     date       value   partial sum
:> > ---+-------------+-------+--------------
:> > 1  | 2002-03-12  |   5   |      5
:> > 2  | 2002-03-13  |   4   |      9
:> > 3  | 2002-03-13  |   7   |     16
:> > 4  | 2002-03-14  |   3   |     19
:> >
:> > As you can see, the row `partial sum' is what I am looking for. Do you
:> > have any idea how can I do it?
:> >
:> > Thanks in advance,
:> >
:> > Marcelo Pereira
:> >
:> > -- Remember that only God and Esc+:w saves.
:> >         __
:> >        (_.\           Marcelo Pereira       |
:> >         / / ___       marcelo@pereira.com   |
:> >        / (_/ _ \__    [Math|99] - IMECC     |
:> > _______\____/_\___)___Unicamp_______________/
:> >
:> >
:> >
:> > ---------------------------(end of broadcast)---------------------------
:> > TIP 5: Have you checked our extensive FAQ?
:> >
:> > http://www.postgresql.org/users-lounge/docs/faq.html
:> >
:>


Re: Sum

From
Jean-Luc Lachance
Date:
Marcelo,

Assuming that "cod" is unique and sequencial, you can use the following:

SELECT *, ( SELECT SUM( value) from table as t2 where t2.cod <= t1.cod)
as running_total
FROM table as t1
ORDER BY cod;

jll


Marcelo Pereira wrote:
>
> Hello All,
>
> I have a single table as:
>
> cod     date       value
> ---+-------------+-------
> 1  | 2002-03-12  |  5
> 2  | 2002-03-13  |  4
> 3  | 2002-03-13  |  7
> 4  | 2002-03-14  |  3
>
> ... and I would like to do a select that do `partial sums'.
>
> So, for instance, I would like to do a select that returns:
>
> cod     date       value   partial sum
> ---+-------------+-------+--------------
> 1  | 2002-03-12  |   5   |      5
> 2  | 2002-03-13  |   4   |      9
> 3  | 2002-03-13  |   7   |     16
> 4  | 2002-03-14  |   3   |     19
>
> As you can see, the row `partial sum' is what I am looking for. Do you
> have any idea how can I do it?
>
> Thanks in advance,
>
> Marcelo Pereira

Re: Sum

From
Marcelo Pereira
Date:
P E R F E C T !!!

Hello Jean-Luc,

Thanks for your answer, it was exacty what I was needing.

Best Regards,

See ya,

Marcelo Pereira

-- Remember that only God and Esc+:w saves.
        __
       (_.\           Marcelo Pereira       |
        / / ___                             |
       / (_/ _ \__    Matematica/99 - IMECC |
_______\____/_\___)___Unicamp_______________/

--- Jean-Luc Lachance, with his fast fingers, wrote:

:> Marcelo,
:>
:> Assuming that "cod" is unique and sequencial, you can use the following:
:>
:> SELECT *, ( SELECT SUM( value) from table as t2 where t2.cod <= t1.cod)
:> as running_total
:> FROM table as t1
:> ORDER BY cod;
:>
:> jll
:>
:>
:> Marcelo Pereira wrote:
:> >
:> > Hello All,
:> >
:> > I have a single table as:
:> >
:> > cod     date       value
:> > ---+-------------+-------
:> > 1  | 2002-03-12  |  5
:> > 2  | 2002-03-13  |  4
:> > 3  | 2002-03-13  |  7
:> > 4  | 2002-03-14  |  3
:> >
:> > ... and I would like to do a select that do `partial sums'.
:> >
:> > So, for instance, I would like to do a select that returns:
:> >
:> > cod     date       value   partial sum
:> > ---+-------------+-------+--------------
:> > 1  | 2002-03-12  |   5   |      5
:> > 2  | 2002-03-13  |   4   |      9
:> > 3  | 2002-03-13  |   7   |     16
:> > 4  | 2002-03-14  |   3   |     19
:> >
:> > As you can see, the row `partial sum' is what I am looking for. Do you
:> > have any idea how can I do it?
:> >
:> > Thanks in advance,
:> >
:> > Marcelo Pereira
:>


Re: Sum

From
Jim Martinez
Date:
After much thought on Mar 28  Jean-Luc Lachance wrote:

> Marcelo,
>
> Assuming that "cod" is unique and sequencial, you can use the following:
>
> SELECT *, ( SELECT SUM( value) from table as t2 where t2.cod <= t1.cod)
> as running_total
> FROM table as t1
> ORDER BY cod;
>
> jll
>

Subqueries outside of the where clause are great!  I hadn't come accross
them when working with the more well advertised database products.

Are they SQL9x compliant?  I try to develop using non postgres specific
tools when I can.

Jim


Re: Sum

From
Jean-Luc Lachance
Date:
Maybe our resident expert on SQL standard, Tom Lane, would care to
answer this one.


Jim Martinez wrote:
>
> After much thought on Mar 28  Jean-Luc Lachance wrote:
>
> > Marcelo,
> >
> > Assuming that "cod" is unique and sequencial, you can use the following:
> >
> > SELECT *, ( SELECT SUM( value) from table as t2 where t2.cod <= t1.cod)
> > as running_total
> > FROM table as t1
> > ORDER BY cod;
> >
> > jll
> >
>
> Subqueries outside of the where clause are great!  I hadn't come accross
> them when working with the more well advertised database products.
>
> Are they SQL9x compliant?  I try to develop using non postgres specific
> tools when I can.
>
> Jim

How to abort a pgaccess query?

From
Jean-Luc Lachance
Date:
Hi all,

Is there a way to abort a pgaccess query?


jll