Cumulative totals

• rs

  SSCrazy

  Points: 2819

  More actions

  June 23, 2006 at 3:09 pm

  #113514

  Hi, I have a table following table

  Date	Val
  6/1/2006	A
  6/2/2006	A
  6/3/2006	A
  6/1/2006	B
  6/2/2006	B
  6/3/2006	B
  6/1/2006	A
  6/2/2006	A
  6/3/2006	A
  6/1/2006	B
  6/2/2006	B
  6/3/2006	B

  I need following result set

  Val	6/1/2006	Total
  A	2	6
  B	2	6

  Is that possible?

  thanks in advance.

   

• Dinakar Nethi-176633

  SSCarpal Tunnel

  Points: 4689

  More actions

  June 23, 2006 at 3:19 pm

  #645682

  You could do a count(*), min(date), val and group by val.

  ******************
  Dinakar Nethi
  Life is short. Enjoy it.
  ******************

• Mike C

  SSC-Insane

  Points: 23224

  More actions

  June 23, 2006 at 5:30 pm

  #645694

  Not exactly what you want, but close:

  CREATE TABLE #test ([ID] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

   [Date] DATETIME,

   Val CHAR(1))

  INSERT INTO #test ([Date], Val)

  SELECT '2006-06-01', 'A'

  UNION ALL SELECT '2006-06-02', 'A'

  UNION ALL SELECT '2006-06-03', 'A'

  UNION ALL SELECT '2006-06-01', 'B'

  UNION ALL SELECT '2006-06-02', 'B'

  UNION ALL SELECT '2006-06-03', 'B'

  UNION ALL SELECT '2006-06-01', 'A'

  UNION ALL SELECT '2006-06-02', 'A'

  UNION ALL SELECT '2006-06-03', 'A'

  UNION ALL SELECT '2006-06-01', 'B'

  UNION ALL SELECT '2006-06-02', 'B'

  UNION ALL SELECT '2006-06-03', 'B'

  SELECT  t1.Val, COUNT(*) AS TotalByDate,  t1.[Date], t2.Total

  FROM #test t1

  INNER JOIN

  (SELECT Val, COUNT(*) AS Total

   FROM #test

   GROUP BY Val) t2

  ON t2.Val = t1.Val

  WHERE t1.[Date] = '2006-06-01'

  GROUP BY t1.Val, t2.Total, t1.[Date]

  DROP TABLE #test