Hi all,

I am trying to write a simple HQL query with an aggregate function and getting an error. Please help.

I have 3 entities Order, OrderItem and Product with the following relationships:

Order --> OrderItem (One to many bi-directional)
OrderItem --> Product (Many to one Uni-directional)

At a high level, a Customer can place an Order that has multiple Order line items with each line item referring to a product and its quantity. Product is like a master table representing the Product catalog.

I am trying to find the total quantity ordered for each product across all Order Items. I am using the following query:

select oi.product.name, sum(oi.quantity) as s1 from OrderItem oi group by oi.product.name order by s1 desc

I am aliasing the sum() function with s1 so that I can sort by the same. But this alias s1 seems to create a problem. Here is the error in the hibernate debug log:

15:39:56,350 DEBUG SQL:111 - select product1_.PRODUCT_NAME as col_0_0_, sum(orderitem0_.ORDER_ITEM_QUANTITY) as col_1_0_ from ORDER_ITEM orderitem0_, PRODUCT product1_ where orderitem0_.PRODUCT_ID=product1_.PRODUCT_ID group by product1_.PRODUCT_NAME order by s1 desc
Hibernate: select product1_.PRODUCT_NAME as col_0_0_, sum(orderitem0_.ORDER_ITEM_QUANTITY) as col_1_0_ from ORDER_ITEM orderitem0_, PRODUCT product1_ where orderitem0_.PRODUCT_ID=product1_.PRODUCT_ID group by product1_.PRODUCT_NAME order by s1 desc
15:39:56,356 DEBUG JDBCExceptionReporter:92 - could not execute query [select product1_.PRODUCT_NAME as col_0_0_, sum(orderitem0_.ORDER_ITEM_QUANTITY) as col_1_0_ from ORDER_ITEM orderitem0_, PRODUCT product1_ where orderitem0_.PRODUCT_ID=product1_.PRODUCT_ID group by product1_.PRODUCT_NAME order by s1 desc]
java.sql.SQLException: Column not found: S1 in statement [select product1_.PRODUCT_NAME as col_0_0_, sum(orderitem0_.ORDER_ITEM_QUANTITY) as col_1_0_ from ORDER_ITEM orderitem0_, PRODUCT product1_ where orderitem0_.PRODUCT_ID=product1_.PRODUCT_ID group by product1_.PRODUCT_NAME order by s1 desc]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:452)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1577)
at org.hibernate.loader.Loader.doQuery(Loader.java:696)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2232)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
at org.hibernate.loader.Loader.list(Loader.java:2124)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1149)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at com.bpp.cm.ProductRepository.getTopFiveProducts(ProductRepository.java:136)
at com.bpp.cm.Main.testGetTop5Products(Main.java:315)
at com.bpp.cm.Main.testProduct(Main.java:233)
at com.bpp.cm.Main.main(Main.java:40)
15:39:56,377 WARN JDBCExceptionReporter:100 - SQL Error: -28, SQLState: S0022
15:39:56,378 ERROR JDBCExceptionReporter:101 - Column not found: S1 in statement [select product1_.PRODUCT_NAME as col_0_0_, sum(orderitem0_.ORDER_ITEM_QUANTITY) as col_1_0_ from ORDER_ITEM orderitem0_, PRODUCT product1_ where orderitem0_.PRODUCT_ID=product1_.PRODUCT_ID group by product1_.PRODUCT_NAME order by s1 desc]
15:39:56,379 DEBUG JDBCTransaction:186 - rollback
15:39:56,379 DEBUG JDBCTransaction:197 - rolled back JDBC Connection
15:39:56,379 DEBUG ConnectionManager:427 - aggressively releasing JDBC connection
15:39:56,380 DEBUG ConnectionManager:464 - releasing JDBC connection [ (open PreparedStatements: 1, globally: 1) (open ResultSets: 0, globally: 0)]
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2235)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
at org.hibernate.loader.Loader.list(Loader.java:2124)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1149)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at com.bpp.cm.ProductRepository.getTopFiveProducts(ProductRepository.java:136)
at com.bpp.cm.Main.testGetTop5Products(Main.java:315)
at com.bpp.cm.Main.testProduct(Main.java:233)
at com.bpp.cm.Main.main(Main.java:40)
Caused by: java.sql.SQLException: Column not found: S1 in statement [select product1_.PRODUCT_NAME as col_0_0_, sum(orderitem0_.ORDER_ITEM_QUANTITY) as col_1_0_ from ORDER_ITEM orderitem0_, PRODUCT product1_ where orderitem0_.PRODUCT_ID=product1_.PRODUCT_ID group by product1_.PRODUCT_NAME order by s1 desc]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:452)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1577)
at org.hibernate.loader.Loader.doQuery(Loader.java:696)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2232)
... 11 more