pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
variables in SQL??
4+ messages / 4 participants
[nested] [flat]

* variables in SQL??
@ 2000-08-16 06:57 Francisco Hernandez <xy0xy0@earthlink.net>
  2000-08-16 11:39 ` Re: [SQL] variables in SQL?? Volker Paul <vpaul@dohle.com>
  2000-08-16 12:11 ` Re: variables in SQL?? DalTech - CTE <CTE@Dal.Ca>
  0 siblings, 2 replies; 4+ messages in thread

From: Francisco Hernandez @ 2000-08-16 06:57 UTC (permalink / raw)
  To: pgsql-sql; pgsql-general@postgresql.org

anyone know if SQL has variables?

what im trying to do is have a Sum of a colum.. as it goes forwards with the
cursor..
like so:

Price|Sum
5|5
4|9
10|19
2|21
7|28

i can do it in the accessing language.. like PHP, Python, Perl etc..
but i wanted to know if the actuall DB could do it?
any ideas?




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] variables in SQL??
  2000-08-16 06:57 variables in SQL?? Francisco Hernandez <xy0xy0@earthlink.net>
@ 2000-08-16 11:39 ` Volker Paul <vpaul@dohle.com>
  2000-08-18 06:12   ` Re: Re: [SQL] variables in SQL?? Craig Johannsen <cjohan@home.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Volker Paul @ 2000-08-16 11:39 UTC (permalink / raw)
  To: Francisco Hernandez <xy0xy0@earthlink.net>; +Cc: pgsql-sql; pgsql-general@postgresql.org

> what im trying to do is have a Sum of a colum.. as it goes forwards with the
> cursor..
> like so:
> 
> Price|Sum
> 5|5
> 4|9
> 10|19
> 2|21
> 7|28

I think what you mean is called running sum, I had the same problem
before, and I found no other solution than creating a column for it,
and calculating its values by a function.

Yours,

V.Paul



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Re: [SQL] variables in SQL??
  2000-08-16 06:57 variables in SQL?? Francisco Hernandez <xy0xy0@earthlink.net>
  2000-08-16 11:39 ` Re: [SQL] variables in SQL?? Volker Paul <vpaul@dohle.com>
@ 2000-08-18 06:12   ` Craig Johannsen <cjohan@home.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Craig Johannsen @ 2000-08-18 06:12 UTC (permalink / raw)
  To: pgsql-sql; +Cc: ian.schumacher@telus.net, Francisco Hernandez <xy0xy0@earthlink.net>; pgsql-general@postgresql.org

You can create a running total provided that you have a unique
sequentially increasing (or decreasing) ID for each row.  See the
following example:

create table tran(id int primary key, price dec(8,2));
insert into tran values(1,5.00);
insert into tran values(2,4.00);
insert into tran values(3,10.00);
insert into tran values(4,2.00);
insert into tran values(5,7.00);

select price, (select sum(price) from tran as d1
where d1.id <= d2.id) as "sum"
from tran as d2;

 price |  sum
-------+-------
  5.00 |  5.00
  4.00 |  9.00
 10.00 | 19.00
  2.00 | 21.00
  7.00 | 28.00
(5 rows)


Francisco wrote:

> > what im trying to do is have a Sum of a colum.. as it goes forwards with the
> > cursor..
> > like so:
> >
> > Price|Sum
> > 5|5
> > 4|9
> > 10|19
> > 2|21
> > 7|28
>

====================================
Craig Johannsen
Critical Path Consulting, Inc.
604-762-1514
http://members.home.net/cjohan/cpath
====================================






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: variables in SQL??
  2000-08-16 06:57 variables in SQL?? Francisco Hernandez <xy0xy0@earthlink.net>
@ 2000-08-16 12:11 ` DalTech - CTE <CTE@Dal.Ca>
  1 sibling, 0 replies; 4+ messages in thread

From: DalTech - CTE @ 2000-08-16 12:11 UTC (permalink / raw)
  To: Francisco Hernandez <xy0xy0@earthlink.net>; +Cc: pgsql-sql

> what im trying to do is have a Sum of a colum.. as it goes forwards

I don't think this is what you want, but I suppose it might help....

Table    = simple
+----------------------------------+----------------------------------+-----
--+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-----
--+
| price                            | int4                             |
4 |
+----------------------------------+----------------------------------+-----
--+
=> select price,count(price) from simple group by price;
price|count
-----+-----
    1|    4
    2|    7
    4|    8
    5|    5
    7|   16
    9|    1
   10|   12
(7 rows)

=> create view simple_v as select price,count(price) from simple group by
price;
CREATE 19503 1
=> select * from simple_v;
price|count
-----+-----
    1|    4
    2|    7
    4|    8
    5|    5
    7|   16
    9|    1
   10|   12
(7 rows)

=> insert into simple (price) values (5);
INSERT 19504 1
=> select * from simple_v;
price|count
-----+-----
    1|    4
    2|    7
    4|    8
    5|    6
    7|   16
    9|    1
   10|   12
(7 rows)




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2000-08-18 06:12 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-16 06:57 variables in SQL?? Francisco Hernandez <xy0xy0@earthlink.net>
2000-08-16 11:39 ` Volker Paul <vpaul@dohle.com>
2000-08-18 06:12   ` Craig Johannsen <cjohan@home.com>
2000-08-16 12:11 ` DalTech - CTE <CTE@Dal.Ca>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox