Rollup and Grouping in SQL

Results 1 to 2 of 2

Thread: Rollup and Grouping in SQL

  1. #1
    Join Date
    Dec 1969

    Default Rollup and Grouping in SQL

    I am working on a report to Summarize the monthly data base on each region. I need a subtotal line after each Region, for example: <BR>Region Month Data <BR>AC 1 4 <BR>AC 2 5 <BR>AC 3 5 <BR>AC subtotal 14 <BR>WE 1 2 <BR>WE 2 3 <BR>WE subtotal 5 <BR>I use the rollup and grouping operation in my SQL code but always give me the syntax error message. I want to know whether because I write my SQL string in ASP instead of I don&#039;t know any of Could anybody help me on this??? <BR><BR>My part of code is like this: <BR>set Conn = Server.Createobject ("ADODB.Connection") <BR>Set rst = Server.CreateObject ("ADODB.Recordset") <BR>DSNstr = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("absenteeism2.mdb") <BR><BR>Conn.Open DSNstr <BR>SQLstr = "SELECT "& _ <BR> " CASE WHEN (GROUPING(tblocation.Region)=1) THEN &#039;MAINTOTAL&#039; ELSE tblocation.Region END AS tblocation.Region, CASE WHEN (GROUPING(tblAbsenteeism.Month)=1) THEN &#039;Subtotal&#039; ELSE tblAbsenteeism.Month END AS tblAbsenteeism.Month,"&_ <BR>" SUM(tblAbsenteeism.Vacation)AS VACATION WHERE tblAbsenteeism.MSA=tblocation.MSA GROUP BY tblocation.Region, tblAbsenteeism.Month WITH ROLLUP" <BR><BR>Thank you so much! <BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Rollup and Grouping in SQL

    Access does not support case expressions nor rollup.<BR><BR>The case can be replaced with iif but the rollup probably must be done in your application.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts