Combining the two sql queries

Question

  First query as follows

select cmn_minor_code as Course_Name, convert(char,cbm_batch_start_dt,106) as Course_date, cbm_batch_id as Batch_ID
from co_batch_master
where cbm_active <. 'd'
and cbm_batch_start_dt between convert(datetime,'06-Jul-2015',6)
and convert(datetime,'06-Jul-2015',6) order by cbm_batch_start_dt

When i run the first query output as follows
Course_Name Course_date Batchid

AFF 12 jun 2015 B8753


Second query as follows

select count(*) as No_of_students
from student s,course_registration cr,batch_course_registration bcr,co_batch_master cbm where cr.stud_id = s.stud_id and cr.cr_bill_no = bcr.cr_bill_no and bcr.bcr_batch_id = cbm.cbm_batch_id and cbm.cbm_active <> 'D' and cr.cr_active = 'A' and s.stud_active <>'D' and cbm_batch_id = 'B8753'

When i run the second query output as follows

No_of_students
24


I want to combine the first query and second query and output i want as follows

Course_Name Course_date Batchid No_of_students

AFF 12 jun 2015 B8753 24 

Answer 1

Hi,

 

Try this query :

 

select cmn_minor_code as Course_Name, convert(char,cbm_batch_start_dt,106) as Course_date, cbm_batch_id as Batch_ID, count(*) as No_of_students
from student s,course_registration cr,batch_course_registration bcr,co_batch_master cbm
where cr.stud_id = s.stud_id
and cr.cr_bill_no = bcr.cr_bill_no
and bcr.bcr_batch_id = cbm.cbm_batch_id
and cbm.cbm_active <> 'D'
and cr.cr_active = 'A'
and s.stud_active <>'D'
and cbm_batch_id = 'B8753'
and cbm.cbm_active < 'd'
and cbm.cbm_batch_start_dt between convert(datetime,'06-Jul-2015',6) and convert(datetime,'06-Jul-2015',6)
group By cbm.cmn_minor_code, convert(char,cbm.cbm_batch_start_dt,106), cbm.cbm_batch_id
order by cbm_batch_start_dt