Hi,

I am using Spring with Hibernate, and try to order a table by the number of values in a column using the following code:
getHibernateTemplate().find(
"select count(v) as num, v.nationality from Visitor v " +
"group by v.nationality order by num"));

However, it doesnt accept my naming, and if doing it without renaming i get a missing group by excepcion.
The following SQL however does work:
SELECT count(*) as numNac, nationality FROM museum_new.museum_visitors m group by nationality order by numNac DESC;
Anybody knows what is happening? I am quite new to Hibernate and HQL.

thanks and best regards
Theo

Hibernate version: 3.2

Full stack trace of any exception that occurs:
com.einsnull.vancouver.data.dao.DAOException: org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
at com.einsnull.vancouver.data.dao.impl.DataAccessExceptionsInterceptor.invoke(DataAccessExceptionsInterceptor.java:33)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy59.getTop25(Unknown Source)
at mx.com.rally.service.variable.NationalityValor.obtenerValores(NationalityValor.java:38)
at mx.com.rally.web.BuscarReporteController.onFormChange(BuscarReporteController.java:258)
at org.springframework.web.servlet.mvc.SimpleFormController.onFormChange(SimpleFormController.java:333)
at org.springframework.web.servlet.mvc.SimpleFormController.processFormSubmission(SimpleFormController.java:262)
at org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal(AbstractFormController.java:265)
at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:856)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:790)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:476)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:441)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:368)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:619)
Caused by: org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:613)
at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:378)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:847)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:839)
at com.einsnull.vancouver.data.dao.impl.CountriesDAOImpl.getTop25(CountriesDAOImpl.java:90)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:301)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.orm.hibernate3.HibernateInterceptor.invoke(HibernateInterceptor.java:104)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at com.einsnull.vancouver.data.dao.impl.DataAccessExceptionsInterceptor.invoke(DataAccessExceptionsInterceptor.java:26)
... 33 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:856)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:373)
... 46 more
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'num' in 'order clause'
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3176)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1266)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 55 more

Name and version of the database you are using: MySQL 4.1.22

The generated SQL (show_sql=true):
Hibernate: select count(visitor0_.email) as col_0_0_, visitor0_.nationality as col_1_0_, country1_.code as code12_ from museum_visitors visitor0_ inner join museum_countries country1_ on visitor0_.nationality=country1_.code group by visitor0_.nationality order by num limit

Hi,

2 things you could try:


or


Rgds

Thanks, it did worked