CODEWITHSUNDEEP
sqlsql-server
Ehsan Shirvan
Ehsan Shirvan

Reputation: 81

Please help me to write this query in SQL Server

Consider this table : 

 dt           qnt 
----------   -------
1               10
2               -2
3               -4
4                3
5               -1
6                5

How do I create a query to get this result? (res is a running total column): 

 dt       qnt       res
----     -----     -----
1         10         10
2         -2          8
3         -4          4
4          3          7
5         -1          6
6          5          11

Upvotes: 0

Views: 95

Answers (3)

Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12555

Use one of this way :

    CREATE TABLE [dbo].[T1]
        (
          [dt] [int] IDENTITY(1, 1)
                     NOT NULL ,
          qnt  [int] NULL ,
          CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED ( [dt] ASC )
            WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                   IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                   ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
        )
    ON  [PRIMARY]
    GO

    INSERT  INTO dbo.T1 ( qnt ) VALUES  ( 10 )
    INSERT  INTO dbo.T1 ( qnt ) VALUES  ( -2 )
    INSERT  INTO dbo.T1 ( qnt ) VALUES  ( -4 )
    INSERT  INTO dbo.T1 ( qnt ) VALUES  ( 3 )
    INSERT  INTO dbo.T1 ( qnt ) VALUES  ( -1 )
    INSERT  INTO dbo.T1 ( qnt ) VALUES  ( 5 )
    GO

    SELECT  * ,
            RunningSum = T1.qnt + COALESCE(( SELECT   SUM(qnt)
                                               FROM     T1 AS T1Sub
                                               WHERE    ( T1Sub.dt < T1.dt )
                                             ), 0)
    FROM    T1

    Go

    SELECT  T1.dt ,
            T1.qnt ,
            SUM(T1Inner.qnt)
    FROM    T1
            INNER JOIN T1 AS T1Inner ON ( T1.dt >= T1Inner.dt )
    GROUP BY T1.dt ,
            T1.qnt
    ORDER BY T1.dt ,
            T1.qnt

    GO

    SELECT  T1.* ,
            T2.RunningSum
    FROM    T1
            CROSS APPLY ( SELECT    SUM(qnt) AS RunningSum
                          FROM      T1 AS CAT1
                          WHERE     ( CAT1.dt <= T1.dt )
                        ) AS T2

    Go

    SELECT  * ,
            RunningSum = ( SELECT   SUM(qnt)
                           FROM     T1 AS T1In
                           WHERE    ( T1In.dt <= T1.dt )
                         )
    FROM    T1  

    Go


    -- In Sql Server 2012
    SELECT  * ,
            SUM(T1.qnt) OVER ( ORDER BY T1.dt 
                               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
                               AS RunningTotal
    FROM    T1



    Drop Table [dbo].[T1]

Upvotes: 2

Joachim Isaksson
Joachim Isaksson

Reputation: 180877

You can do it using a simple subquery that calculates the sum up to the current row, which should work well on any version of SQL Server;

SELECT dt, qnt, 
  (SELECT SUM(qnt) FROM Table1 ts WHERE ts.dt <= t1.dt) res
FROM Table1 t1
ORDER BY dt;

An SQLfiddle to test with.

If you're using SQL Server 2012, see Amit's answer for a more efficient query.

Upvotes: 5

Amit Singh
Amit Singh

Reputation: 8109

If you are Using sql Server 2012 than you can try like this.

Select * ,Sum([qnt]) Over(order by dt) from table1

Sql Fiddle Demo

Upvotes: 2

Related Questions