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

Question

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 ?

Update it with the number of students for that subject on average per year? Or the # of students for that subject in a particular year? — Brian DeMilia
– Brian DeMilia, Commented Sep 29, 2014 at 1:37
This is an example of a question that would greatly benefit from sample data and desired results. — Gordon Linoff
– Gordon Linoff, Commented Sep 29, 2014 at 1:38
@Harshana what columns are on the SUBJECT table? Is there a year column on that table? — Brian DeMilia
– Brian DeMilia, Commented Sep 29, 2014 at 1:42
@BrianDeMilia need to update stdCount column of subject table from studentCount of Unit table — Harshana
– Harshana, Commented Sep 29, 2014 at 1:44

Brian DeMilia · Accepted Answer · 2014-09-29 01:43:18Z

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)

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related