CODEWITHSUNDEEP
sqlsybase
Zanam
Zanam

Reputation: 4807

Cummulative left overs using SQL

I am using an older version of sybase which does not have keywords like lead.

I want to find cumulative left over.

Let me try to explain this with an example.

StartQuantity = 500

Category   Time        Quantity        LeftOver
cat1      10:01:01.000     100           400 
cat1      10:01.01.001     50            350
cat1      10:01:01.002      0            350
cat1      10:01.01.003    100            250
cat1      10:01:03.001    100            150

Similar rows are available in cat2 and the number of rows per category is variable.

Please note that in above I have arranged the rows in ascending order of time.

Upvotes: 0

Views: 206

Answers (2)

Gopal Sanodiya
Gopal Sanodiya

Reputation: 106

SELECT Category, SUM(500-(Quantity)) FROM table_name group by Category

OR

SELECT Category,(count(1)*500)-sum(Qunatity) FROM table_name group by Category

Upvotes: 0

user330315
user330315

Reputation:

I don't know if Sybase supports this, but in PostgreSQL, Oracle and others you can do

select category,
       time,
       quantity, 
       500 - sum(quantity) over (partition by category order by time) as leftover
from the_unknown_table;
order by category, time

Upvotes: 1

Related Questions