List tables in a single SQL Server query

Asked

Viewed 320 times

2

I need a query that returns to each map the players who had battles on it, the amount of battles each player had (on the respective map), the total time played (per player on the respective map), and the amount of transgressions (per player on the respective map). The tables are: Classification, Battles and trangressor_list and are structured as the following:

Classification

Id | Player_id | Battle_id | Time_played

Battles

Id | Map_id

trangressor_list

Id | Transgressor_id | Battle_id

The wish I tried was this::

SELECT Map_Id, Player_Id, COUNT(Player_Id) AS Quantidade, SUM(Time_Played) AS 'Time Played'
FROM classification, battles, trangressor_list
WHERE  classification.Battle_Id = battles.Id AND trangressor_list.Battle_Id = battles.Id
GROUP BY Player_Id, Map_Id
ORDER BY Map_Id ASC, Player_Id ASC
;

this works partially but is not exactly what I need

sql-server
  • 1

    Does the Transgressor _Id column contain player identification? // In the same battle, can the player have more than one transgression? // You can add sample data to test?

  • Yes, Transgressor _Id contains player identification and may have more than one transgression per battle

2 answers

2


Using

SELECT subq.*
      ,QtdTransgressoes = (SELECT COUNT(DISTINCT tra.Transgressor_Id)
                             FROM classification as cla
                             JOIN trangressor_list as tra 
                                  ON tra.Battle_Id = cla.Battle_Id
                            WHERE cla.Player_Id = subq.Player_Id)
  FROM (SELECT Map_Id
              ,Player_Id
              ,COUNT(Player_Id) AS Quantidade
              ,SUM(Time_Played) AS TimePlayed
         FROM classification
         JOIN battles ON classification.Battle_Id = battles.Id
        GROUP BY Player_Id, Map_Id) as subq
  ORDER BY Map_Id ASC, Player_Id ASC

Using CTE

WITH Tab1 (Map_Id, Player_Id, Quantidade, TimePlayed)
AS
(
     SELECT Map_Id
           ,Player_Id
           ,COUNT(Player_Id) 
           ,SUM(Time_Played) 
      FROM classification
      JOIN battles ON classification.Battle_Id = battles.Id
     GROUP BY Player_Id, Map_Id
)
,
Tab2 (Player_Id, QtdTransgressoes)
AS
(
     SELECT cla.Player_Id
           ,COUNT(DISTINCT tra.Transgressor_Id)
       FROM classification as cla
       JOIN trangressor_list as tra ON tra.Battle_Id = cla.Battle_Id
      GROUP BY cla.Player_Id
 )
 SELECT Tab1.*
       ,Tab2.QtdTransgressoes
   FROM Tab1
   LEFT JOIN Tab2 ON Tab2.Player_Id = Tab1.Player_Id
  ORDER BY Map_Id ASC, Player_Id ASC

1

Considering that in a battle a player may have at most one transgression recorded, here is suggestion:

-- código #1
SELECT B.Map_Id, C.Player_Id, 
       count(B.Id) as [Qtd batalhas],
       sum(C.Time_Played) as [Tempo jogado],
       count(T.Id) as [Qtd transgressões]
  from battles as B
       inner join classification as C on C.Battle_Id = B.Id
       left join trangressor_list as T on T.Transgressor_Id = C.Player_Id
                                          and T.Battle_Id = B.Id
  group by B.Map_Id, C.Player_Id;

If there is the possibility of more than one transgression per battle (for the same player), changes the way they are accounted for.

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.