Manoj Muduli Manoj Muduli - 2 months ago 6
MySQL Question

Find number of items left based upon credit's id and all ids prior

I have a

t
variable which contains 100 and one table .

that table name
credit
and it contains the following data

Id
1
2
3


I would like the result set to look like this:

result
99 (100 - 1)
97 (100 - 2 - 1)
94 (100 - 3 - 2 - 1)


So far, I have been able to use the following code successfully:

set @t=100;
select @t:=@t-id as result from credit;


Is there a way to do this without using a variable?

Answer

This is quite simple and you shouldn't have to use the variable at all:

SELECT 100-(SELECT SUM(c2.id) FROM credit c2 WHERE c2.id <= c.id)
FROM credit c;

Here is a SQL Fiddle for you: http://sqlfiddle.com/#!9/1fc3c6/6

The subquery simply gets the sum of all numbers including, and prior to the credit id.

Comments