need help on running total problem (self-join, I assume)

• mark-720552

  Grasshopper

  Points: 15

  More actions

  July 2, 2008 at 6:04 pm

  #72346

  given a sales table defined as:

  CREATE TABLE [dbo].[t_sales](

  [sales_id] [int] IDENTITY(1,1) NOT NULL,

  [cust_id] [int] NOT NULL,

  [sales_amt] [money] NOT NULL,

  [sale_date] [datetime] NOT NULL

  ) ON [PRIMARY]

  I need to return the cust_id, sales_amt, and the running total of the sales_amt, which should reset when the cust_id changes. Something like this:

  cust_id, amt, running_total

  1,50,50

  1,25,75

  1,50,125

  2,25,25

  2,50,75

  etc.

  I've spent hours trying to get this to work, but I'm clearly missing something. Help greatly appreciated.

• PrabodhM

  Ten Centuries

  Points: 1304

  More actions

  July 2, 2008 at 10:58 pm

  #837595

  Try it out....

  select t1.sales_id, t1.cust_id, t1.sales_amt, sum(t2.sales_amt) as c from t_sales t1, t_sales t2

  where t1.sales_id >= t2.sales_id AND t1.cust_id = t2.cust_id

  group by t1.sales_id, t1.cust_id, t1.sales_amt

  order by t1.cust_id