Column Sum in Select Query

Question

I have problem in sql server select query. I have following table.

ID-----Name----Quantity
1-------a-----------10
2-------b-----------30
3-------c-----------20
4-------d-----------15
5-------e-----------10
6-------f-----------30
7-------g-----------40

I want to select those record where the sum of Quantity < value. For example if I say select those record where the Quantity sum <65 then the output will be

ID-----Name----Quantity
1-------a-----------10
2-------b-----------30
3-------c-----------20

because if we include the next record then the sum of Quantity will 75.

I want to create this query. Please help me out.

Answer 1

You can simply use a correlated subquery to do so, and it will work fine for both MySQL, and SQL Server. But it is not the best performance wise solution:

SELECT 
  ID, 
  Name,
  Quantity
FROM
(
  SELECT 
    t1.ID, 
    t1.Name,
    t1.Quantity,
    (SELECT SUM(t2.Quantity)
     FROM tablename AS t2
     WHERE t2.ID <= t1.ID) AS Total
  FROM Tablename AS t1
) AS t
WHERE Total < 65;

See it in action:

• SQL Fiddle Demo

This will give you:

| ID | NAME | QUANTITY |
------------------------
|  1 |    a |       10 |
|  2 |    b |       30 |
|  3 |    c |       20 |