6

I have two database tables, Team (ID, NAME, CITY, BOSS, TOTALPLAYER) and Player (ID, NAME, TEAMID, AGE), the relationship between the two tables is one to many, one team can have many players.

I want to know is there a way to define a TOTALPLAYER column in the Team table as computed?

For example, if there are 10 players' TEAMID is 1, then the row in Team table which ID is 1 has the TOTALPLAYER column with a value of 10. If I add a player, the TOTALPLAYER column's value goes up to 11, I needn't to explicitly assign value to it, let it generated by the database. Anyone know how to realize it?

Thx in advance.

BTW, the database is SQL Server 2008 R2

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
James
  • 2,570
  • 7
  • 34
  • 57

2 Answers2

9

Yes, you can do that - you need a function to count the players for the team, and use that in the computed column:

CREATE FUNCTION dbo.CountPlayers (@TeamID INT)
RETURNS INT 
AS BEGIN
    DECLARE @PlayerCount INT

    SELECT @PlayerCount = COUNT(*) FROM dbo.Player WHERE TeamID = @TeamID

    RETURN @PlayerCount
END

and then define your computed column:

ALTER TABLE dbo.Team
ADD TotalPlayers AS dbo.CountPlayers(ID)

Now if you select, that function is being called every time, for each team being selected. The value is not persisted in the Team table - it's calculated on the fly each time you select from the Team table.

Since it's value isn't persisted, the question really is: does it need to be a computed column on the table, or could you just use the stored function to compute the number of players, if needed?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Do you mean the function will execute everytime when I query a team out? – James Mar 14 '12 at 06:12
  • 2
    @HeroIverson3: yes - it will be executed on every SELECT to the table that includes that column (including for each `SELECT * FROM Team`) – marc_s Mar 14 '12 at 06:13
  • Is there another way to realize it? I want the column only update when I add or delete a player. when I query a team out, only like query other tables, without the function execute. Thanks in advance! – James Mar 14 '12 at 06:23
  • Or, my design is not a proper method? – James Mar 14 '12 at 06:24
  • 1
    @HeroIverson3: to achieve an update only when something changes, you would need to have a trigger on the `Player` table. But that always has the risk of not working properly - in that case, I would probably just compute the number of players using the function (or an equivalent subselect) when needed. – marc_s Mar 14 '12 at 07:38
  • 1
    @HeroIverson3: Seconding that. If you had a static updatable `TotalPlayers` column in `Team`, you would have to make sure that, for safety's sake, the column was never touched by any explicit `UPDATE` but the one in the trigger (and to ensure the initial `0`, you would probably need to specify a `DEFAULT` constraint and, again for safety, always omit the column in your `INSERT` statements). – Andriy M Mar 14 '12 at 08:17
  • @marc_s, thanks for your reply! Add a function is a good way to do that, or I needn't add such a column, only compute the total number in query by joining the two tables? – James Mar 14 '12 at 08:27
  • @Andriy M, thanks for your reply! Add a function is a good way to do that, or I needn't add such a column, only compute the total number in query by joining the two tables? – James Mar 14 '12 at 08:27
  • 1
    @HeroIverson3: Unless it was a huge database, I would go with a dynamically computed value. A static computed column in the table using a function like in marc_s's answer might do, but I would only use this approach if the tables were fairly small. Otherwise I would compute the value in my queries. A compromise solution might be to define a view based on a query along the lines of laser_dude's. – Andriy M Mar 14 '12 at 08:39
  • Is the `ID` in the `alter` statement from the `players` or from the `team` table? – Wairimu Murigi Jul 22 '15 at 12:15
  • @WairimuMurigi: since it's a computed column on the **`Team`** table, that `ID` also is from the `Team` table – marc_s Jul 22 '15 at 13:23
2

You don't have to store the total in the table -- it can be computed when you do a query, something like:

SELECT teams.*, COUNT(players.id) AS num_players
FROM teams LEFT JOIN  players ON teams.id = players.team_id
GROUP BY teams.id;

This will create an additional column "num_players" in the query, which will be a count of the number of players on each team, if any.

Andriy M
  • 76,112
  • 17
  • 94
  • 154
laser_dude
  • 41
  • 4
  • 2
    Basically yes, but this wouldn't compile in SQL Server, because the GROUP BY column list must include all the non-aggregated columns referenced in the SELECT clause. – Andriy M Mar 14 '12 at 08:33