Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How can I write this sql query in as hql:

select count(distinct as prepaid, count(distinct 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.

share|improve this question
Why have you tagged with both [mysql] and [sql-server]? – ypercubeᵀᴹ Feb 12 '14 at 11:18
You are also missing the ON part in the join. – ypercubeᵀᴹ Feb 12 '14 at 11:31

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

    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.

share|improve this answer

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
share|improve this answer
The CASE expressions should be inside the COUNT() functions. – ypercubeᵀᴹ Feb 12 '14 at 13:03
@ypercube why it should be inside count function.. – Shahid Iqbal Feb 13 '14 at 4:45
You can try it out. You can't reference ispostpaid (outside of an aggregate) when that column is not in the GROUP BY list. – ypercubeᵀᴹ Feb 13 '14 at 8:44
i have executed it, its working fine in SQL SERVER. – Shahid Iqbal Feb 13 '14 at 10:52
Oh sorry, I didn't see the GROUP BY ispostpaid, did you have it when you first posted the answer? Yes, it should work with that GROUP BY, but the OP does not have such a group by in his question. – ypercubeᵀᴹ Feb 13 '14 at 10:57

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.