how to count number of student

• Virtual Reality

  Ten Centuries

  Points: 1182

  More actions

  May 10, 2016 at 4:05 pm

  #316441

  Hello,

  I need to modify one report.

  And client requirement is to put number of students in that report.

  This one is my existing query.

  SELECT section_master.yr_cde,

  section_master.trm_cde,

  section_schedules.room_cde,

  section_schedules.bldg_cde,

  section_schedules.loc_cde,

  section_schedules.crs_cde,

  section_master.short_crs_title_1,

  section_schedules.monday_cde,

  section_schedules.tuesday_cde,

  section_schedules.wednesday_cde,

  section_schedules.thursday_cde,

  section_schedules.friday_cde,

  section_schedules.saturday_cde,

  section_schedules.sunday_cde,

  section_schedules.begin_tim,

  section_schedules.end_tim,

  section_schedules.days_tim_ovrid,

  section_schedules.begin_dte,

  section_schedules.end_dte,

  section_schedules.comment_txt,

  section_schedules.seq_num_2,

  section_master.x_listed_parnt_crs,

  count(STUDENT_CRS_HIST.ID_NUM) "No of student" // I couldn't put count function here.how could I count number of students?

  FROM section_master,

  section_schedules,

  STUDENT_CRS_HIST

  where ( section_schedules.yr_cde = section_master.yr_cde ) and

  ( section_schedules.trm_cde = section_master.trm_cde ) and

  ( section_schedules.crs_cde = section_master.crs_cde ) and

  (SECTION_SCHEDULES.YR_CDE=STUDENT_CRS_HIST.YR_CDE) and

  (SECTION_SCHEDULES.TRM_CDE=STUDENT_CRS_HIST.TRM_CDE) and

  (SECTION_SCHEDULES.CRS_CDE=STUDENT_CRS_HIST.CRS_CDE) and

  (section_master.YR_CDE=STUDENT_CRS_HIST.YR_CDE) and

  (SECTION_MASTER.TRM_CDE=STUDENT_CRS_HIST.TRM_CDE) and

  (SECTION_MASTER.CRS_CDE=STUDENT_CRS_HIST.CRS_CDE)

  How could I make changes in this query and modify with number of student column per course name?

  I want to count number of student existence in a particular course.

  Thanks

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  May 10, 2016 at 5:11 pm

  #1878012

  For this you would use GROUP BY.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• Virtual Reality

  Ten Centuries

  Points: 1182

  More actions

  May 12, 2016 at 3:36 pm

  #1878794

  I tried to use group by but it gives me different rows than original one

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 12, 2016 at 3:48 pm

  #1878796

  Read and follow the steps in the following article, you will get better answers to your questions:

  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Pulivarthi Sasidhar

  SSCertifiable

  Points: 6706

  More actions

  May 13, 2016 at 1:00 am

  #1878828

  Please post your modified Query...

  I thought you have applied for one column only...

• Virtual Reality

  Ten Centuries

  Points: 1182

  More actions

  May 13, 2016 at 8:40 am

  #1878921

  I applied this query:

  SELECT SM.yr_cde,

  SM.trm_cde,

  SS.room_cde,

  SS.bldg_cde,

  SS.loc_cde,

  SS.crs_cde,

  SM.short_crs_title_1,

  SS.monday_cde,

  SS.tuesday_cde,

  SS.wednesday_cde,

  SS.thursday_cde,

  SS.friday_cde,

  SS.saturday_cde,

  SS.sunday_cde,

  SS.begin_tim,

  SS.end_tim,

  SS.days_tim_ovrid,

  SS.begin_dte,

  SS.end_dte,

  SS.comment_txt,

  SS.seq_num_2,

  SM.x_listed_parnt_crs,

  count(SCH.ID_NUM) "No of student" -- I couldn't put count function here.how could I count number of students?

  FROM dbo.section_master As SM

  Inner Join dbo.section_schedules As SS On SS.yr_cde = SM.yr_cde

  and SS.trm_cde = SM.trm_cde

  and SS.crs_cde = SM.crs_cde

  Inner Join dbo. STUDENT_CRS_HIST As SCH On SS.YR_CDE = SCH.YR_CDE

  and SS.TRM_CDE = SCH.TRM_CDE

  and SS.CRS_CDE = SCH.CRS_CDE

  and SM.YR_CDE = SCH.YR_CDE

  and SM.TRM_CDE = SCH.TRM_CDE

  and SM.CRS_CDE = SCH.CRS_CDE

  Group by SM.yr_cde,

  SM.trm_cde,

  SS.room_cde,

  SS.bldg_cde,

  SS.loc_cde,

  SS.crs_cde,

  SM.short_crs_title_1,

  SS.monday_cde,

  SS.tuesday_cde,

  SS.wednesday_cde,

  SS.thursday_cde,

  SS.friday_cde,

  SS.saturday_cde,

  SS.sunday_cde,

  SS.begin_tim,

  SS.end_tim,

  SS.days_tim_ovrid,

  SS.begin_dte,

  SS.end_dte,

  SS.comment_txt,

  SS.seq_num_2,

  SM.x_listed_parnt_crs;

  When I was applying original query (below query)

  SELECT section_master.yr_cde,

  section_master.trm_cde,

  section_schedules.room_cde,

  section_schedules.bldg_cde,

  section_schedules.loc_cde,

  section_schedules.crs_cde,

  section_master.short_crs_title_1,

  section_schedules.monday_cde,

  section_schedules.tuesday_cde,

  section_schedules.wednesday_cde,

  section_schedules.thursday_cde,

  section_schedules.friday_cde,

  section_schedules.saturday_cde,

  section_schedules.sunday_cde,

  section_schedules.begin_tim,

  section_schedules.end_tim,

  section_schedules.days_tim_ovrid,

  section_schedules.begin_dte,

  section_schedules.end_dte,

  section_schedules.comment_txt,

  section_schedules.seq_num_2,

  section_master.x_listed_parnt_crs

  --count(STUDENT_CRS_HIST.ID_NUM) "No of student" -- I couldn't put count function here.how could I count number of students?

  FROM section_master,

  section_schedules,

  STUDENT_CRS_HIST

  where ( section_schedules.yr_cde = section_master.yr_cde ) and

  ( section_schedules.trm_cde = section_master.trm_cde ) and

  ( section_schedules.crs_cde = section_master.crs_cde ) and

  (SECTION_SCHEDULES.YR_CDE=STUDENT_CRS_HIST.YR_CDE) and

  (SECTION_SCHEDULES.TRM_CDE=STUDENT_CRS_HIST.TRM_CDE) and

  (SECTION_SCHEDULES.CRS_CDE=STUDENT_CRS_HIST.CRS_CDE) and

  (section_master.YR_CDE=STUDENT_CRS_HIST.YR_CDE) and

  (SECTION_MASTER.TRM_CDE=STUDENT_CRS_HIST.TRM_CDE) and

  (SECTION_MASTER.CRS_CDE=STUDENT_CRS_HIST.CRS_CDE);

  I was getting 66660 results and now using group by result reduce to 5545.

  What does make difference?

  I donot know.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 13, 2016 at 9:01 am

  #1878932

  RV16 (5/13/2016)

  ---

  I applied this query:

  SELECT SM.yr_cde,

  SM.trm_cde,

  SS.room_cde,

  SS.bldg_cde,

  SS.loc_cde,

  SS.crs_cde,

  SM.short_crs_title_1,

  SS.monday_cde,

  SS.tuesday_cde,

  SS.wednesday_cde,

  SS.thursday_cde,

  SS.friday_cde,

  SS.saturday_cde,

  SS.sunday_cde,

  SS.begin_tim,

  SS.end_tim,

  SS.days_tim_ovrid,

  SS.begin_dte,

  SS.end_dte,

  SS.comment_txt,

  SS.seq_num_2,

  SM.x_listed_parnt_crs,

  count(SCH.ID_NUM) "No of student" -- I couldn't put count function here.how could I count number of students?

  FROM dbo.section_master As SM

  Inner Join dbo.section_schedules As SS On SS.yr_cde = SM.yr_cde

  and SS.trm_cde = SM.trm_cde

  and SS.crs_cde = SM.crs_cde

  Inner Join dbo. STUDENT_CRS_HIST As SCH On SS.YR_CDE = SCH.YR_CDE

  and SS.TRM_CDE = SCH.TRM_CDE

  and SS.CRS_CDE = SCH.CRS_CDE

  and SM.YR_CDE = SCH.YR_CDE

  and SM.TRM_CDE = SCH.TRM_CDE

  and SM.CRS_CDE = SCH.CRS_CDE

  Group by SM.yr_cde,

  SM.trm_cde,

  SS.room_cde,

  SS.bldg_cde,

  SS.loc_cde,

  SS.crs_cde,

  SM.short_crs_title_1,

  SS.monday_cde,

  SS.tuesday_cde,

  SS.wednesday_cde,

  SS.thursday_cde,

  SS.friday_cde,

  SS.saturday_cde,

  SS.sunday_cde,

  SS.begin_tim,

  SS.end_tim,

  SS.days_tim_ovrid,

  SS.begin_dte,

  SS.end_dte,

  SS.comment_txt,

  SS.seq_num_2,

  SM.x_listed_parnt_crs;

  When I was applying original query (below query)

  SELECT section_master.yr_cde,

  section_master.trm_cde,

  section_schedules.room_cde,

  section_schedules.bldg_cde,

  section_schedules.loc_cde,

  section_schedules.crs_cde,

  section_master.short_crs_title_1,

  section_schedules.monday_cde,

  section_schedules.tuesday_cde,

  section_schedules.wednesday_cde,

  section_schedules.thursday_cde,

  section_schedules.friday_cde,

  section_schedules.saturday_cde,

  section_schedules.sunday_cde,

  section_schedules.begin_tim,

  section_schedules.end_tim,

  section_schedules.days_tim_ovrid,

  section_schedules.begin_dte,

  section_schedules.end_dte,

  section_schedules.comment_txt,

  section_schedules.seq_num_2,

  section_master.x_listed_parnt_crs

  --count(STUDENT_CRS_HIST.ID_NUM) "No of student" -- I couldn't put count function here.how could I count number of students?

  FROM section_master,

  section_schedules,

  STUDENT_CRS_HIST

  where ( section_schedules.yr_cde = section_master.yr_cde ) and

  ( section_schedules.trm_cde = section_master.trm_cde ) and

  ( section_schedules.crs_cde = section_master.crs_cde ) and

  (SECTION_SCHEDULES.YR_CDE=STUDENT_CRS_HIST.YR_CDE) and

  (SECTION_SCHEDULES.TRM_CDE=STUDENT_CRS_HIST.TRM_CDE) and

  (SECTION_SCHEDULES.CRS_CDE=STUDENT_CRS_HIST.CRS_CDE) and

  (section_master.YR_CDE=STUDENT_CRS_HIST.YR_CDE) and

  (SECTION_MASTER.TRM_CDE=STUDENT_CRS_HIST.TRM_CDE) and

  (SECTION_MASTER.CRS_CDE=STUDENT_CRS_HIST.CRS_CDE);

  I was getting 66660 results and now using group by result reduce to 5545.

  What does make difference?

  I donot know.

  Help us help you:

  Lynn Pettis (5/12/2016)

  ---

  Read and follow the steps in the following article, you will get better answers to your questions:

  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  May 13, 2016 at 12:04 pm

  #1879004

  Looks like you just need to "window" the count function and loose the group by...

  COUNT(TableName.StudentID) OVER (PARTITION BY NULL)

  HTH,

  Jason

• Virtual Reality

  Ten Centuries

  Points: 1182

  More actions

  May 13, 2016 at 1:13 pm

  #1879032

  Thanks for your suggestion. I looked at this forum and I will post question according to that.

• Virtual Reality

  Ten Centuries

  Points: 1182

  More actions

  May 13, 2016 at 1:19 pm

  #1879034

  Thanks.

  You mean If I want to count number of students according to crs_cde then I should write like this

  count(sch.id_num) OVER (PARTITION BY ss.CRS_CDE) "No of student"

  I just want to verify with you.

  Thanks

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  May 16, 2016 at 8:14 am

  #1879256

  RV16 (5/13/2016)

  ---

  Thanks.

  You mean If I want to count number of students according to crs_cde then I should write like this

  count(sch.id_num) OVER (PARTITION BY ss.CRS_CDE) "No of student"

  I just want to verify with you.

  Thanks

  Aside from "No of student" being truly cringewothy... Yes, that would be correct.

• R.P.Rozema

  SSChampion

  Points: 12317

  More actions

  May 17, 2016 at 6:39 am

  #1879481

  Jason A. Long (5/16/2016)

  ---

  RV16 (5/13/2016)

  ---

  Thanks.

  You mean If I want to count number of students according to crs_cde then I should write like this

  count(sch.id_num) OVER (PARTITION BY ss.CRS_CDE) "No of student"

  I just want to verify with you.

  Thanks

  Aside from "No of student" being truly cringewothy... Yes, that would be correct.

  🙂

  Jason should have said: No, the correct notation is:

  count(sch.id_num) OVER (PARTITION BY ss.CRS_CDE) AS [No of student]

  Where the keyword "AS" is in fact optional, but for clarity is better not left off.

  Also, I suggest you study more modern notations for the various join types too. This will help you understand joins much better. Plus it is a lot easier for us to read, which will help you get your answers faster.

  But, the given suggestion seems far too complicated. Your teacher will see very quickly that you did not come up with that solution yourself. I suggest you restudy your books on the following topics: aliases, join clauses and subqueries, so you can perfectly explain the following solution:

  SELECT

  sm.yr_cde,

  sm.trm_cde,

  ss.room_cde,

  ss.bldg_cde,

  ss.loc_cde,

  ss.crs_cde,

  sm.short_crs_title_1,

  ss.monday_cde,

  ss.tuesday_cde,

  ss.wednesday_cde,

  ss.thursday_cde,

  ss.friday_cde,

  ss.saturday_cde,

  ss.sunday_cde,

  ss.begin_tim,

  ss.end_tim,

  ss.days_tim_ovrid,

  ss.begin_dte,

  ss.end_dte,

  ss.comment_txt,

  ss.seq_num_2,

  sm.x_listed_parnt_crs,

  (

  SELECT count(*)

  FROM STUDENT_CRS_HIST sch

  WHERE sch.YR_CDE = ss.YR_CDE

  AND sch.TRM_CDE = ss.TRM_CDE

  AND sch.CRS_CDE = ss.CRS_CDE

  ) AS [No of student]

  FROM section_master AS sm

  INNER JOIN section_schedules AS ss ON ss.yr_cde = sm.yr_cde AND ss.trm_cde = sm.trm_cde AND ss.crs_cde = sm.crs_cde

  ---

  
  Posting Data Etiquette - Jeff Moden[/url]
  Posting Performance Based Questions - Gail Shaw[/url]
  Hidden RBAR - Jeff Moden[/url]
  Cross Tabs and Pivots - Jeff Moden[/url]
  Catch-all queries - Gail Shaw[/url]
  

  ---

  
  If you don't have time to do it right, when will you have time to do it over?

• Virtual Reality

  Ten Centuries

  Points: 1182

  More actions

  May 25, 2016 at 4:26 pm

  #1881469

  R.P.Rozema (5/17/2016)

  ---

  Jason A. Long (5/16/2016)

  ---

  RV16 (5/13/2016)

  ---

  Thanks.

  You mean If I want to count number of students according to crs_cde then I should write like this

  count(sch.id_num) OVER (PARTITION BY ss.CRS_CDE) "No of student"

  I just want to verify with you.

  Thanks

  Aside from "No of student" being truly cringewothy... Yes, that would be correct.

  🙂

  Jason should have said: No, the correct notation is:

  count(sch.id_num) OVER (PARTITION BY ss.CRS_CDE) AS [No of student]

  Where the keyword "AS" is in fact optional, but for clarity is better not left off.

  Also, I suggest you study more modern notations for the various join types too. This will help you understand joins much better. Plus it is a lot easier for us to read, which will help you get your answers faster.

  But, the given suggestion seems far too complicated. Your teacher will see very quickly that you did not come up with that solution yourself. I suggest you restudy your books on the following topics: aliases, join clauses and subqueries, so you can perfectly explain the following solution:

  SELECT

  sm.yr_cde,

  sm.trm_cde,

  ss.room_cde,

  ss.bldg_cde,

  ss.loc_cde,

  ss.crs_cde,

  sm.short_crs_title_1,

  ss.monday_cde,

  ss.tuesday_cde,

  ss.wednesday_cde,

  ss.thursday_cde,

  ss.friday_cde,

  ss.saturday_cde,

  ss.sunday_cde,

  ss.begin_tim,

  ss.end_tim,

  ss.days_tim_ovrid,

  ss.begin_dte,

  ss.end_dte,

  ss.comment_txt,

  ss.seq_num_2,

  sm.x_listed_parnt_crs,

  (

  SELECT count(*)

  FROM STUDENT_CRS_HIST sch

  WHERE sch.YR_CDE = ss.YR_CDE

  AND sch.TRM_CDE = ss.TRM_CDE

  AND sch.CRS_CDE = ss.CRS_CDE

  ) AS [No of student]

  FROM section_master AS sm

  INNER JOIN section_schedules AS ss ON ss.yr_cde = sm.yr_cde AND ss.trm_cde = sm.trm_cde AND ss.crs_cde = sm.crs_cde