Hibernation issue: must appear in a GROUP BY clause or be used in an aggregate function
The request I want to run:
SELECT date_trunc('month',UA.activity_date),SUM(UA.points) FROM user_activity UA
WHERE UA.activity_date > '01/01/2014' AND UA.activity_date < '12/31/2014'
GROUP BY date_trunc('month',UA.activity_date)
ORDER BY date_trunc('month',UA.activity_date)
Expected results:
03/01/2014 00:00:00 66.04000000000000000 05/01/2014 00:00:00 13.50000000000000000 07/01/2014 00:00:00 27.00000000000000000 08/01/2014 00:00:00 26.00000000000000000 09/01/2014 00:00:00 13.50000000000000000
When I use Hibernate to execute this query, I get this error:
ERROR: column "useractivi0_.activity_date" must appear in the GROUP BY clause or be used in an aggregate function
To fix this, I modified the above query as:
SELECT date_trunc('month',UA.activity_date),SUM(UA.points) FROM user_activity UA
WHERE UA.activity_date > '01/01/2014' AND UA.activity_date < '12/31/2014'
GROUP BY date_trunc('month',UA.activity_date),***UA.activity_date***
ORDER BY date_trunc('month',UA.activity_date)
But the results are wrong:
03/01/2014 00:00:00 25.40000000000000200 03/01/2014 00:00:00 25.40000000000000200 03/01/2014 00:00:00 15.24000000000000000 05/01/2014 00:00:00 13.50000000000000000 07/01/2014 00:00:00 9.00000000000000000 07/01/2014 00:00:00 18.00000000000000000 08/01/2014 00:00:00 4.50000000000000000
How can I fix this?
When I run the first request from my toad shell directly to the db it seems to work fine. However, when I run the same query from code using Hibernate, I get this error.
+4
source to share
2 answers
You can try to have your sum and date_trunc in a subquery, something like below:
SELECT colA, SUM(colB) FROM (SELECT date_trunc('month',UA.activity_date) colA,SUM(UA.points) colB FROM user_activity UA
WHERE UA.activity_date > '01/01/2014' AND UA.activity_date < '12/31/2014'
GROUP BY UA.activity_date
ORDER BY date_trunc('month',UA.activity_date))sub GROUP BY colA
Just repeat the group substitution. See if this helps.
0
source to share