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