Create an SQL computed column

Question

I need some help trying to create a computed column that references another table.

Table1 example (Consoles):

Console   Games     GameCount
PS4       Y         3
Switch    Y         5
Xbox      Y         2

Table2 example (Games):

GameName  Console
GameA     PS4
GameB     Switch
GameC     Switch
GameD     Xbox
GameE     PS4
GameF     Switch
GameG     Switch
GameH     PS4
GameI     Switch
GameJ     Xbox

I need to create a GameCount column in Table 1 (Consoles) that would look at Table 2 and count the games per console.

You need a View not a computed column to show this data. [SQL Server Views](https://learn.microsoft.com/en-us/sql/relational-databases/views/views?view=sql-server-ver15) — M.Ali, Oct 22 '21 at 10:13
There are bodges to do this as a computed column via a scalar function. However, you should avoid these because, first and foremost, they fail to update as the data in Table2 changes, and would only update if something forced a change to happen in Table1. — Damien_The_Unbeliever, Oct 22 '21 at 10:30

Parth M. Dave · Answer 1 · 2021-10-22T11:14:57.360

I think As per my basic understanding you just want below output :

UPDATE TABLE1 ON TABLE1.GameCount=Table2Output.GameCount FROM TABLE1
LEFT JOIN (SELECT Console,Count(*) AS GameCount FROM TABLE2 GROUP BY Console) AS Table2Output ON Table2Output.Console=TABLE1.Console

SELECT TABLE2.GamerName,TABLE2.Console,ISNULL(TABLE1.GameCount,0) FROM TABLE2
LEFT JOIN TABLE1 ON TABLE1.Console=TABLE2.Console

Correct me if i am wrong.

Create an SQL computed column

1 Answers1