Column Sum in Select Query in SQL

Total Post:70

Points:494
 1439  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. Post:28

    Points:196
    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 |

      Modified On Mar-31-2018 05:20:11 AM

Answer

NEWSLETTER

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