SQL query issue for count function on a subquery

Question

I have two tables

BatchDetails

BatchCode    MaxStd
------------------
B001          12
B002          14
B003          10

AdmissionBatch

Batch  Rollno
---------------
B001    1
B001    2
B002    3
B003    4
B003    5

I need the BatchCode of those batches are not have max students. I wrote a query but it is not working

select 
    batchCode, MaxStd, count(rollno)  
from 
    BatchDetails as a 
join 
    AdmissionBatch as b on a.batchcode = b.batch  
group by  
    batchcode, maxStd 
having 
    count(rollno) < maxSTD

This query is not working because if there is not student in a particular batch then the batch will not appear

I tried subquery as well but no help

Please help

Maybe one of the answers are your solution already, but I do have doubts... What exactly are you trying to achieve? Let's say, that there is no row in BatchDetails with a BatchCode B002. What would be the expected output? What, if the MaxStd is zero (or NULL)? — Gottfried Lesigang
– Gottfried Lesigang, Commented Mar 1, 2016 at 9:53

Shantanu Gupta · Accepted Answer · 2016-03-01 09:46:00Z

1

Use left join instead of inner join.

select batchCode,MaxStd, count(rollno)  from BatchDetails as a 
LEFT JOIN AdmissionBatch  as b on a.batchcode=b.batch  
group by batchcode,maxStd 
having count(rollno)< maxSTD

answered Mar 1, 2016 at 9:46

Shantanu Gupta

21.2k56 gold badges187 silver badges294 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user5992977 · Accepted Answer · 2016-03-01 09:46:22Z

1

You need a left join to keep the results even when there's no match :

select batchCode,MaxStd, count(rollno)
from BatchDetails as a 
left outer join AdmissionBatch  as b
 on a.batchcode=b.batch  
group by batchcode,maxStd 
having count(rollno)< maxSTD

answered Mar 1, 2016 at 9:46

user5992977

Collectives™ on Stack Overflow

SQL query issue for count function on a subquery

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related