Re: [sqlite] Re: How do I do this

[miguel manese]

On Jan 14, 2008 9:09 AM, Vishal Mailinglist <[EMAIL PROTECTED]> wrote:
> > sno | id | amount
> > > 1| 1 |  200
> > > 2| 1 | 300
> > > 3   |  2 | 100
> > > 4  | 2 | 100
> > > 5 | 1 | 500
> What if I do not have control over sno i.e it is  random or unpredictable ,
> I want to subtract it in order of occurrence. Like doing subtracion of sno 2
> and 5 and so on may be next occurance for id 1 is at sno 20 , then what.

The simple solution is to select both rows separately and then
subtract it in your application. Really, if it were me I'd do that.

Or you can use sum(), the only problem is how to make the the amount
to be subtracted negative. Something like below, where the amount with
the lower sno is turned negative.

select sum(case when sno=(select min(sno) from tableName where (sno=?
and id=?) or (sno=? and id=?)) then -amount else amount end)
from tableName where (sno=? and id=?) or (sno=? and id=?)

M. Manese

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: How do I do this

[Vishal Mailinglist]

Hi ,

I knew I was not explaining better.

> sno | id | amount
> > 1| 1 |  200
> > 2| 1 | 300
> > 3   |  2 | 100
> > 4  | 2 | 100
> > 5 | 1 | 500
> >
> > how could I subtract the sno 1 id 1 and sno 2 id 1 amount .
>
> select
> (select amount from tableName where sno=1 and id=1) -
> (select amount from tableName where sno=2 and id=1);
>
>
What if I do not have control over sno i.e it is  random or unpredictable ,
I want to subtract it in order of occurrence. Like doing subtracion of sno 2
and 5 and so on may be next occurance for id 1 is at sno 20 , then what.


-- 
Regards,
Vishal Kashyap.
Need help visit
http://help.vishal.net.in

[sqlite] Re: How do I do this

[Igor Tandetnik]

Vishal Mailinglist  wrote:

Did not know how to do this on a database that I have in sqlite

sno | id | amount
1| 1 |  200
2| 1 | 300
3   |  2 | 100
4  | 2 | 100
5 | 1 | 500

how could I subtract the sno 1 id 1 and sno 2 id 1 amount .


select
   (select amount from tableName where sno=1 and id=1) -
   (select amount from tableName where sno=2 and id=1);

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-