none
create view with updating each row

    Question

  • S.No	Invoice	Amount	total
    1	S2231	1100	1100
    2	S2123	1500	2600
    3	S3121	2000	4600

    I want to create view with total column. The total column should be sum of previous total and the amount.

    Note: Total column is not in the table.

    Please suggest me.


    Saturday, October 19, 2013 11:30 AM

Answers

  • try,

    create table test_invoice (sno int,invoice varchar(10),amount Int)
    insert into test_invoice values(1,'S2231',	1100),(2,'S2123',	1500),(3,'S3121',2000)
    go
    create view test_vw
    as
    
    select SNo,	Invoice	,Amount,( select SUM(amount) from test_invoice B where B.sno<=A.sno) as	total from
    test_invoice A
    go
    select * from test_vw


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Saturday, October 19, 2013 2:14 PM
  • Hi Karthick,

    Here is another way of getting running totals;

    CREATE VIEW dbo.InvoiceDetails
    AS
    SELECT t1.SNo, t1.Invoice, t1.Amount, SUM(t2.Amount) Total
    FROM dbo.Invoice t1
    	CROSS JOIN dbo.Invoice t2
    WHERE t2.SNo <= t1.SNo
    GROUP BY t1.SNo, t1.Invoice, t1.Amount
    GO
    
    SELECT * FROM dbo.InvoiceDetails
    ORDER BY SNo

    This is what I have used with many cases, however check the performance against other technique before implementing this.

    There is easy way if you use SQL Server 2012;

    CREATE VIEW dbo.InvoiceDetails
    AS
    SELECT SNo, Invoice, Amount, SUM(Amount) OVER (ORDER BY SNo) Total
    FROM dbo.Invoice
    GROUP BY SNo, Invoice, Amount
    GO
    
    SELECT * FROM dbo.InvoiceDetails
    ORDER BY SNo

    -----------------------------------

    Dinesh Priyankara
    http://dinesql.blogspot.com/


    Sunday, October 20, 2013 9:51 AM

All replies