Join two subquery in hql

Question

How can I write this sql query in as hql:

select count(distinct s.id) as prepaid, count(distinct ss.id) as postpaid
from (select * from subscriber where subscriber.ispostpaid=0) s 
join (select * from subscriber where ispostpaid=1) ss 
where s.subscriber_status='active';

Or can make this query without using join by using only subquery.

Answer 1

Assuming I understand what you're looking to return, and assuming subscriber.id is unique in the table (seems likely), here is a SQL Server answer (though it should be adaptable to MySQL):

select
    isnull(sum((case when s.ispostpaid = 0 and s.subscriber_status = 'active' then 1 else 0 end)), 0) as prepaid,
    isnull(sum((case when s.ispostpaid = 1 then 1 else 0 end)), 0) as postpaid
    from subscriber s;

This selectively counts the rows of interest with one table access instead of an expensive cross join and grouping in the original query. The use of ISNULL is to still return counts of 0 if the table is empty.

Using NHibernate, I believe this could also be written using ICriteria... but it's been a while for me on that. Check out my answer here, as it might help if that's what you're looking for.

Answer 2

try this..

SELECT     CASE WHEN ispostpaid=0 AND subscriber_status='active' THEN COUNT(DISTINCT S.ID) END AS PrePaid,
           CASE WHEN ispostpaid=1 THEN COUNT(DISTINCT S.ID) END AS PostPaid
FROM       Subscriber
GROUP BY   ispostpaid