CODEWITHSUNDEEP
mysqlsql
Queue
Queue

Reputation: 456

SQL Derived Column

I currently have TableOne with one column: ID. I want to add a derived second column, NumSemesters which should be the number of occurrences of TableOne_ID in a second table, TableTwo.

Essentially, NumSemesters should be the number of times TableOne.ID = TableTwo.Student_ID.

How do I alter my first table to add this second column?

In table one, ID is the primary key.

Upvotes: 0

Views: 179

Answers (2)

Jim Buckley Barret
Jim Buckley Barret

Reputation: 330

I think you are looking for something like this.

SELECT TableOne.ID, TableTwo.NumSemesters FROM TableOne INNER JOIN (SELECT ID, COUNT(*) AS NumSemesters FROM TableOne GROUP BY ID) as TableTwo ON TableOne.ID = TableTwo.ID

Jim

Upvotes: 0

flyingmeatball
flyingmeatball

Reputation: 7997

Something like this (assuming you already created the column NumSemesters):

update TableOneset NumSemesters = b.idCount
from TableOne a left join 
(select Student_ID, count(Student_ID) as 'idCount' from TableTwo groupby Student_ID ) b on a.ID = b.Student_ID

Upvotes: 1

Related Questions