Problem with running totals in SQL Server

• williaal

  SSC Rookie

  Points: 37

  More actions

  June 6, 2008 at 2:41 pm

  #187724

  Hi Everyone,

  I am trying to do a simple running total, but i am also doing a conversion to an int as well as a case statement.

  I am getting an error near the "AS 'RunningTotal'"

  Does anyone have any suggestions or somments on a way i can do this better or do it perioud? Thanks for the help.

  SELECT S.IssuedDateTime,

  S.ResponseId,

  Convert (int, MAX(CASE WHEN S.DisplaySequence = 1 THEN S.Response END)) AS DailyReading

  (SELECT SUM(DailyReading)

  FROM z_v_Information

  WHERE (ResponseId <= S.ResponseId)) AS 'RunningTotal'

  FROM z_v_Information S

  GROUP BY S.IssuedDateTime, S.SurveyResponseId

  Order BY S.IssuedDateTime;

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  June 6, 2008 at 2:45 pm

  #825729

  You need a comma "," after DailyReading. Then it should at least parse.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• dva2007

  SSCertifiable

  Points: 7707

  More actions

  June 10, 2008 at 7:11 am

  #826606

  try this.

  SELECT S.IssuedDateTime,

  S.ResponseId,

  Convert (int, MAX(CASE WHEN S.DisplaySequence = 1 THEN S.Response END)) AS DailyReading

  SUM(Convert (int, MAX(CASE WHEN S.DisplaySequence = 1 THEN S.Response END)))

  FROM z_v_Information

  WHERE ResponseId <= S.ResponseId

  GROUP BY S.IssuedDateTime, S.SurveyResponseId

  Order BY S.IssuedDateTime;