-1

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?

grepsedawk
  • 3,324
  • 2
  • 26
  • 49
Man
  • 742
  • 1
  • 6
  • 23
  • 1
    show what is in credit (id). Show the table, and the expected results – Drew Sep 09 '16 at 00:41
  • Look at the tables in [this answer](http://stackoverflow.com/a/37472510) or [this question](http://stackoverflow.com/q/37940132). Do a better job of explaining – Drew Sep 09 '16 at 00:43
  • if i have 10 pens. i sold this pen to three people first people buy 1 pens second people buy 2 pens third people buy 3 pens i have result set of selling number of pens that credit table id column and i want to result set how many pens are not sell per people – Man Sep 09 '16 at 01:56
  • You want 99,97,94. Your query got that. What is the question? That you want an approach that does NOT use variables? – Drew Sep 09 '16 at 01:58

1 Answers1

1

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.

grepsedawk
  • 3,324
  • 2
  • 26
  • 49