Click here to Skip to main content
15,795,793 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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

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

for that how can i combine the above first and second query and get the output in sql server.

[edit]Code block added - OriginalGriff[/edit]
Updated 11-Jul-15 3:15am

1 solution

If I understood your question correctly, you're after GROUP BY[^] clause.

If you take the first query and join the tables to the tables in the second and then group by all non-aggregated columns, I believe that we're getting close to what you want. Also all of the conditions need to be merged into the same statement.

So perhaps something like this:
SELECT  cbm.cmn_minor_code as Course_Name, 
        CONVERT(char,cbm.cbm_batch_start_dt,106) as Course_date, 
        cbm.cbm_batch_id as Batch_ID,
        count(*)  as No_of_students
FROM co_batch_master cbm,
     student s,
     course_registration cr,
     batch_course_registration bcr
WHERE cbm.cbm_active   < 'd'
AND   cbm.cbm_batch_start_dt 
      BETWEEN CONVERT(datetime,'06-Jul-2015',6) 
      AND     CONVERT(datetime,'06-Jul-2015',6)  
AND   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'
GROUP BY cbm.cmn_minor_code, 
ORDER BY cbm_batch_start_dt

Note that I'm really not sure about the joins nor the other conditions since I have no idea about your table structure or logic. However, this should get you started.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900