# Re: help on sql

Date: Fri, 9 Oct 2009 20:29:17 -0700 (PDT)

Message-ID: <2668891e-369b-4fe1-a9d9-86fbddc63614_at_o41g2000yqb.googlegroups.com>

On Oct 9, 10:39 pm, RA <ang..._at_gmail.com> wrote:

*> Hi sql gurus,*

*>*

*> I need some help and also curious on how to write this in single sql*

*> statement, requirements goes like this*

*>*

*> Table structure*

*> ================*

*> create table find_fixed_open (*

*> find number,*

*> fixed number,*

*> open number,*

*> bug_when date*

*> );*

*>*

*> Data*

*> =============*

*> insert into find_fixed_open values(5,3,2,'10/01/2009');*

*> insert into find_fixed_open values(52,38,16,'10/02/2009');*

*> insert into find_fixed_open values(68,45,39,'10/03/2009');*

*> insert into find_fixed_open values(112,59,92,'10/04/2009');*

*> insert into find_fixed_open values(45,12,125,'10/05/2009');*

*>*

*> Formula*

*> =========*

*> Open = Find - fixed + previous rows open(basically open is cumulative)*

*> i.e.*

*> 5 - 3 + 0 = 2*

*> 52 - 38 + 2 = 16*

*> 68 - 45 + 16 = 39*

*> 112 - 59 + 39 = 92*

*> 45 - 12 + 92 = 125*

So is this the result you want:

2

16

39

92

125

??????

or a single summand, as in

294

??????????

(Hint to other posters: clear descriptions of your problem go a long way to getting help)

*>*

*> Please help me and enlighten me.*

*>*

*> Thank you*

*> Raju*

Aside from OPEN being a keyword,

Break the problem down into steps.

The individual counts can be obtained using
SELECT (find-fixed+open) opencount from find_fixed_open;

Assuming you wanted the individual counts, you can stop here.

That basically gives you a table of the individual counts. Now given that table, how would you compute the sum? Here's a hint: SELECT SUM(opencount) ...

Assuming you want the single summation, can you finish the work by putting these hints together into one statement?

**HTH,
**

Ed
Received on Fri Oct 09 2009 - 22:29:17 CDT