Home > DeveloperSection > Forums > Column Sum in Select Query in SQL
lois waisbrooker
lois waisbrooker

Total Post:70

Points:494
Posted on    October-05-2013 3:39 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 879  View(s)
Rate this:

I have trouble with sql server select query. I have following table.

ID-----Name----Quantity
1-------Jhon-----------10
2-------Jay -----------30
3-------Nik-----------20
4-------Carlos----------15
5-------Rikki-----------10
6-------Mark-----------30
7-------Amr-----------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-------Jhon-----------10
2-------Jay-----------30
3-------Nik-----------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.



F Scott Fitzgerald
F Scott Fitzgerald

Total Post:29

Points:203
Posted on    October-05-2013 4:25 AM

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;

Output: -

This will give you:

| ID | NAME | QUANTITY |
------------------------
|  1 |    Jhon |       10 |
|  2 |    Jay|       30 |
|  3 |    Nik|       20 |

Don't want to miss updates? Please click the below button!

Follow MindStick