To help us answer this question can you put a line of code like this in your script just before each LABKEY.Query.executeSql

  console.log(sqlQuery)

then run the code using some javascipt debugger (e.g. Firebug in firefox).

Seeing the actual generated SQL would be helpful

Here is the response.

{
    "exceptionClass": "java.sql.SQLException",
    "exception": "Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.",
    "stackTrace": [
        "net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)",
        "net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)",
        "net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)",
        "net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)",
        "net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)",
        "net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1301)",
        "org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:205)",
        "org.labkey.api.data.ConnectionWrapper$StatementWrapper.executeQuery(ConnectionWrapper.java:1282)",
        "org.labkey.api.data.Table._executeQuery(Table.java:102)",
        "org.labkey.api.data.Table.executeQuery(Table.java:267)",
        "org.labkey.api.data.Table.selectForDisplay(Table.java:1419)",
        "org.labkey.api.data.Table.selectForDisplay(Table.java:1399)",
        "org.labkey.api.data.RenderContext.selectForDisplay(RenderContext.java:357)",
        "org.labkey.api.data.RenderContext.getResultSet(RenderContext.java:234)",
        "org.labkey.api.data.DataRegion.getResultSet(DataRegion.java:592)",
        "org.labkey.api.data.DataRegion.getResultSet(DataRegion.java:576)",
        "org.labkey.api.query.QueryView.exportToApiResponse(QueryView.java:1197)",
        "org.labkey.api.action.ApiQueryResponse.<init>(ApiQueryResponse.java:67)",
        "org.labkey.query.controllers.QueryControllerSpring$ExecuteSqlAction.execute(QueryControllerSpring.java:1493)",
        "org.labkey.query.controllers.QueryControllerSpring$ExecuteSqlAction.execute(QueryControllerSpring.java:1414)",
        "org.labkey.api.action.ApiAction.handleRequest(ApiAction.java:146)",
        "org.labkey.api.action.BaseViewAction.handleRequestInternal(BaseViewAction.java:162)",
        "org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)",
        "org.labkey.api.action.SpringActionController.handleRequest(SpringActionController.java:336)",
        "org.labkey.api.module.DefaultModule.dispatch(DefaultModule.java:689)",
        "org.labkey.api.view.ViewServlet.service(ViewServlet.java:139)",
        "javax.servlet.http.HttpServlet.service(HttpServlet.java:802)",
        "org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)",
        "org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)",
        "org.labkey.api.data.TransactionFilter.doFilter(TransactionFilter.java:36)",
        "org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)",
        "org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)",
        "org.labkey.core.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:124)",
        "org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)",
        "org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)",
        "org.labkey.api.module.ModuleLoader.doFilter(ModuleLoader.java:647)",
        "org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)",
        "org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)",
        "org.labkey.api.security.AuthFilter.doFilter(AuthFilter.java:101)",
        "org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)",
        "org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)",
        "org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)",
        "org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)",
        "org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)",
        "org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)",
        "org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)",
        "org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)",
        "org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)",
        "org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)",
        "org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)",
        "org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)",
        "org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)",
        "java.lang.Thread.run(Thread.java:619)"
    ]
}

OK, I know what that means. Can you include a few more bits of information a) labkey server version b) sql server version c) the actual SQL you submitted on this request?

Thanks

here are the answers to your questions:

a)
Core Database Configuration
Server URL    jdbc:jtds:sqlserver://10.1.1.102/labkey
Product Name    Microsoft SQL Server
Product Version    09.00.2047
JDBC Driver Name    jTDS Type 4 JDBC Driver for MS SQL Server and Sybase
JDBC Driver Version    1.2.2
 
Runtime Information
Mode    Production
Servlet Container    Apache Tomcat/5.5.20
Java Runtime    1.6.0_12
Java Home    C:\jdk1.6\jre
Username    SYSTEM
User Home Dir    C:\Documents and Settings\Default User
Webapp Dir    C:\Program Files\LabKey Server\labkeywebapp
OS    Windows 2003

b)MS SQL 2005 Std with SP1.

c) there is a txt file with the original request that contains the code used in the wiki.

thanks in advance for the information

I reopened an old related bug

https://www.labkey.org/issues/home/Developer/issues/details.view?issueId=6250

We are looking to upgrade to 9.3 soon. Is this still and issue in the newer versions. as i had indicated it worked very nicely on an 8.3 version with postgres so it may be an MS SQL bug.

The original bug was reported against SQL Server only in 8.3 and fixed in 9.2. I can't tell what version of labkey you repro'd with from the information above. If it is 9.2 then it's probably fixed, but I'm going to verify locally.

that should have said "If it is _older_ than 9.2".

I tried some ad-hoc testing with 10.1 and found no problems and verified that we have some similar queries in our junit tests from release 9.2.

BTW since 10.1 is close to release, so that might be an option for upgrading instead of 9.3.