COLUMN SUM IN SELECT QUERY IN SQL

lois waisbrooker

Total Post:70

Points:494
Posted by  lois waisbrooker
 1178  View(s)
Ratings:
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.

  1. F Scott Fitzgerald

    Post:29

    Points:203
    Re: Column Sum in Select Query in SQL

    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 |

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!