List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 13 1999 12:46am
Subject:Re: Enumerating rows and cumulating values
View as plain text  
On Wed, 1999-08-25 09:43:43 -0300, Dino wrote:
> How can I do a query do get results like this:
> 
> num  Name        value  cumulated
> 1    Something   20.00   20.00
> 2    Other        5.00   25.00
> 3    another      2.00   27.00
> 
> I mean, the num field and cumulated is not on the table.
> Could be something like this:
> 
> "select enum(),name,value,cumulated(value) from table"

SELECT SUM(t1.name>=t2.name && t1.value>=t2.value) AS num
     , t1.*
     , SUM(t2.value * (t1.name>=t2.name && t1.value>=t2.value)) AS cumulated
FROM yourtable AS t1
   , yourtable AS t2
GROUP BY t1.name, t1.value;
+-----+-----------+-------+-----------+
| num | name      | value | cumulated |
+-----+-----------+-------+-----------+
|   1 | another   |  2.00 |      2.00 |
|   2 | Other     |  5.00 |      7.00 |
|   3 | Something | 20.00 |     27.00 |
+-----+-----------+-------+-----------+

But please, THIS IS NOT MEANT AS A SERIOUS ANSWER, because it's much
easier and faster to calculate the columns 'num' and 'cumulated' on
your application side, as Sinisa wrote.  The query above is more like
a hack ... just for your amusement. ;-)

As homework find out, why it does what it does!  And why it only works,
if there are no duplicates in 'yourtable'. :-)

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Smybolic Links as dataRobert Canary23 Aug
  • Re: Smybolic Links as dataJim Faucette23 Aug
  • Re: Smybolic Links as dataThimble Smith23 Aug
  • Re: Smybolic Links as dataRobert Canary23 Aug
    • Re: Smybolic Links as dataThimble Smith23 Aug
  • Enumerating rows and cumulating valuesDino25 Aug
    • Enumerating rows and cumulating valuessinisa25 Aug
    • Re: Enumerating rows and cumulating valuesMartin Ramsch13 Sep
      • Re: Enumerating rows and cumulating valuesManuel Coral A.16 Sep
        • Re: Enumerating rows and cumulating valuesJames Manning16 Sep
          • Re: Enumerating rows and cumulating valuesMartin Ramsch16 Sep
        • Re: Enumerating rows and cumulating valuesMartin Ramsch16 Sep