forum

Home / DeveloperSection / Forums / Column Sum in Select Query in SQL

Ask Question

Column Sum in Select Query in SQL

Anonymous User210505-Oct-2013

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.

mssql servermssql server 
Updated on 05-Oct-2013
Anonymous User

Anonymous User

Follow
I am a content writter !

Can you answer this question?

Answer

1 Answers

Anonymous User

Anonymous User

05-Oct-2013

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|

Liked By