Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

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

2 Answers

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.

share|improve this answer
add comment

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
1  
The CASE expressions should be inside the COUNT() functions. –  ypercube Feb 12 at 13:03
    
@ypercube why it should be inside count function.. –  Shahid Iqbal Feb 13 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 at 8:44
    
i have executed it, its working fine in SQL SERVER. –  Shahid Iqbal Feb 13 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 at 10:57
show 2 more comments

Your Answer

 
discard

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.