Hello, I have such a situation, I need to add another sample from another table (statisticsTable) to the main sample in the players table. The bottom line is that the player has a lot of statistics for each game, and I need to display additional columns in sums over several fields. like that

select sum(StatisticsTable.Falls) as 'Фолов за всю жизнь' from StatisticsTable, Players where (Players.PlayerID = StatisticsTable.PlayerID)

the query for the first table itself looks like this

 select Players.FirstName as 'Имя', Countries.NationalityName as 'Национальность', Teams.TeamName as 'Команда', Positions.PositionName as 'Позиция', datepart(year,getdate())-datepart(year, Convert(Varchar, DateOfBurn, 104)) as 'Возраст' from Players, Countries, Positions, Teams where ((Players.CountryID = Countries.CountryID) and (Players.TeamID = Teams.TeamID) and (Players.PositionID = Positions.PositionID))

But simply putting UNION between these queries results in the error "Message 205, level 16, All requests combined with the UNION , INTERSECT or EXCEPT operators must have the same number of expressions in the target lists." Please help me correctly combine these 2 samples

  • While it is not very clear what requests combine. Do you combine the two queries presented in the question? Or the last two similar requests? - cheops
  • one
    I don’t understand at all why you ask questions, even if you don’t read (or completely ignore) comments under your old questions. I tried to find out why you are casting a date to a string when calculating age, and you repeat the same error in this query ... in general, it would be more correct to get age as datediff(year, DateOfBurn, getdate()) - Mike
  • I pointed out the link in the previous question to the previous question, where the answer was conversion to a string, and datediff (year, DateOfBurn, getdate ()) will return somewhere 1904 (if the date of birth is 1997) because I'm not in the datetime format, but simply date - simply good
  • + It seemed to me that that question was no longer relevant, I read all the comments, but it seemed to me that I stated everything in my question. - simply good
  • one
    Those. you keep exactly the year of birth, i.e. the number 1997? Or is it all the same data type date and it contains a full birthday? In the first case, you need to stupidly do datepart(year,getdate())-1997 , in the second, what I wrote above will work fine - Mike

1 answer 1

The union you are trying to apply is used to get additional rows in the selection, not additional columns. Of course, all rows should have the same number of columns.

If I understand correctly, you want to get a column where there will be a sum of certain statistics for each player (why I don’t have no group by in the first request, he gave you the sum for all players, which is strange in the context of players).

 select Players.FirstName as 'Имя', Countries.NationalityName as 'Национальность', Teams.TeamName as 'Команда', Positions.PositionName as 'Позиция', datepart(year,getdate())-datepart(year, Convert(Varchar, DateOfBurn, 104)) as 'Возраст', (select sum(StatisticsTable.Falls) from StatisticsTable where (Players.PlayerID = StatisticsTable.PlayerID) ) as 'Фолов за всю жизнь' from Players, Countries, Positions, Teams where ((Players.CountryID = Countries.CountryID) and (Players.TeamID = Teams.TeamID) and (Players.PositionID = Positions.PositionID))

If you need to select many columns from the statistics table, then it is better to rewrite the query like this:

 select Players.FirstName as 'Имя', Countries.NationalityName as 'Национальность', Teams.TeamName as 'Команда', Positions.PositionName as 'Позиция', datepart(year,getdate())-datepart(year, Convert(Varchar, DateOfBurn, 104)) as 'Возраст', Stat.Falls as 'Фолов за всю жизнь', Stat.xyz as 'Еще какая то статистика' from Players, Countries, Positions, Teams, (select PlayerID, sum(StatisticsTable.Falls) as Falls, sum(xyz) as xyz from StatisticsTable group by PlayerID ) Stat where Players.CountryID = Countries.CountryID and Players.TeamID = Teams.TeamID and Players.PositionID = Positions.PositionID and Players.PlayerID = Stat.PlayerID
  • Yes, thank you very much, it never even occurred to me to do so - simply good
  • But here's another 1 question, judging by your query, then I sample the statistics table, and after the query I get 1 column. It turns out that if I still need 1 field, I will have to make another 1 request, and I need to calculate about 10 fields. In the same way, I will have to sample 10 times from that table, it looks somehow not economical in time. Is it possible to optimize the query? - simply good
  • I did not think that the answer could contain a repeated sample for each column, so I indicated that only 1 is needed for compactness. It turned out badly :( - simply good
  • Thank you very much! - simply good