CODEWITHSUNDEEP
javapostgresqlhibernate
boycod3
boycod3

Reputation: 5313

must appear in the GROUP BY clause or be used in an aggregate function for count postgresql

select count(*) from CustomerOrder co where  
 co.createdAt >= :fromDate and co.createdAt <= :toDate order by co.createdAt desc;

I have above count HQl query while I executing I'm getting GROUP BY clause or be used in an aggregate function

Upvotes: 1

Views: 140

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133400

The aggregations functions are not affected by order clause so you obtain the correct result without adding an order by. you can't a selected column not mentioned in group by and not involved in aggregation. In your case the order by createdAt work as the createdAt is selected and this raise the error

so if you want the count for your where condition you just need

select count(*) 
from CustomerOrder co 
where  co.createdAt >= :fromDate and co.createdAt <= :toDate

otherwise if you want an aggregated result for each createdAt then you must add this column the a group by clause

select createdAt , count(*) 
from CustomerOrder co 
where  co.createdAt >= :fromDate and co.createdAt <= :toDate 
GROUP BY createdAt

in this case you obtain one row for each createdAt with the proper count

Upvotes: 2

Related Questions