title: how to merge count values in sql
body: <p>I am trying to update all city values of the table team as: “city” + “ #p” + “number of players” +” g” + “number of goals forward” (e.g. “Tokyo #p25 g74”).</p>
  
  <p>I tried to do this and have this two queries. One for get the number of players and one for get the number of goals.</p>
  
  <p>Query for the number of players:</p>
  
  <pre><code>select  t.city + '#p' + CONVERT(varchar(10), count(pt.playerId)) + '#g' 
  from    team        t, 
          player_team pt 
  where   pt.teamID = t.teamID 
  group By t.teamId,t.city
  </code></pre>
  
  <p>Query for the number of goals:</p>
  
  <pre><code>select  count(*) totalgoals,
          pt.teamID 
  from    goals       g, 
          player_team pt 
  where   g.playerId = pt.playerId 
  group by pt.teamID
  </code></pre>
  
  <p>i couldn't merge theese two counts.
  Help me out pls...</p>
  
  <p>Also my tables hierarchy and fields like the shown below</p>
  
  <pre><code>player 
  (
      playerID        int, 
      firstName       nvarchar(25), 
      lastName        nvarchar(25), 
      nationality     varchar(25), 
      birthDate       smalldatetime, 
      age             smallint, 
      position        varchar(25)
  )
  
  team 
  (
      teamID  int,
      name    nvarchar(50), 
      city    nvarchar(25)
  )
  
  player_team 
  (
      playerID    int, 
      teamID      int, 
      season      varchar(5)
  )
  
  match 
  (
      matchID         int,
      homeTeamID      int, 
      visitingTeamID  int, 
      dateOfMatch     smalldatetime, 
      week            tinyint
  )
  
  goals 
  (
      matchID     int, 
      playerID    int, 
      isOwnGoal   bit, 
      minute      tinyint
  )
  </code></pre>
  
  <p>EDIT : Select query with the given below is worked well and give me the right results.But how can i update the table with this multiple records? When i try to update it as a subquery into update statement, it gives me compile error and complaining about multirecords...</p>
  
  <p>Error:Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as an expression.</p>