CODEWITHSUNDEEP
sqlsql-server
Harshana
Harshana

Reputation: 23

How to write sql statement to newly added column with another table values?

I have table called subject and I added a new column stdCount.

There is another table called enrollments with columns student_id, subject, year, marks. Mainly this table records for each student which subjects he or she is enrolled in.

Now I need to get the student count for each unit from the enrollments table and update the subject table's stdCount column.

How can I do this ?

Upvotes: 1

Views: 48

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13248

If the SUBJECT table has one row per subject, even if that subject is offered in 2+ years, and you want STDCOUNT to show the total number of students enrolled in all years throughout all of time:

update subject s
   set stdcount =
       (select count(*)
          from enrollments e
         where e.subject = s.subject)

If the SUBJECT table has one row per subject and year (and has a year column), and you want to show the total number of students enrolled in each (subject, year) combination:

update subject s
   set stdcount =
       (select count(*)
          from enrollments e
         where e.subject = s.subject
           and e.year = s.year)

Upvotes: 2

Related Questions