JPQL aggregate expression based on object property

Question

I don't know if JPQL support the query like this(I am using EclipseLink 2.4.1):

select count(product.id if product.pics.count>0) as proWithPic,count(product.id if product.pics.count=0) as proWithoutPic from Product product group by product.brandName.

I know the syntax is ugly, please correct me.

Thanks

Answer 1

I would execute two queries.

select count(product.id) as proWithPic from Product product where size(product.pics) > 0 group by product.brandName

select count(product.id) as proWithoutPic from Product product where size(product.pics) = 0 group by product.brandName

There might be a way to execute them as a single query using sub-selects in the SELECT clause, or UNION, but two queries would be much simpler and probably perform better.