Problema na criação de uma Function

Question

Meu nível em sql é básico - intermediário volta e meia me deparo com problemas que não entendo por que acontecem. A função que estou criando faz basicamente uma pesquisa da tabela Users para retornar uma pequena lista do ranking dos usuários, por exemplo, se eu pesquisar o Rank 3 vai retornar uma lista do 1 ate o 10:

CREATE FUNCTION [dbo].[RankRefreshGeneral](@IdRank INT)
RETURNS @Return TABLE
(
	Id INT,
	RankGeneral INT,
	Name NVARCHAR(15),
	League NVARCHAR(15),
	Title NVARCHAR(15),
	Honor INT,
	Prestige INT,
	Cities INT,
	Tributary INT,
	Population INT
)
AS
BEGIN
	DECLARE @L INT = @IdRank / 10;
	INSERT @Return
	SELECT U.Id,U.RankGeneral,U.Name,L.Name,T.Name,U.Honor,U.Prestige,Count(C.Id),Count(CT.Id),Sum(C.Population)
	FROM Leagues AS L, Titles AS T, Cities AS C, Cities AS CT,
	(SELECT * FROM Users WHERE RankGeneral >= @L * 10 AND RankGeneral <= (@L * 10) + 10) AS U
	WHERE L.Id = U.IdLeague AND T.Id = U.IdTitle AND C.IdOwner = U.Id AND CT.IdSovereign = C.Id
	RETURN
END

Erro: "Line 19 Column 'U.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Ah só mais uma coisa porque com Count(C.*) da erro?

Answer 1

Olá Mateus,

Na sua query você está usando uma funções de agrupamento (Count e Sum), porém não está definindo um GROUP BY. Segue a sua query modificada para funcionar:

CREATE FUNCTION [dbo].[RankRefreshGeneral](@IdRank INT)
RETURNS @Return TABLE
(
	Id INT,
	RankGeneral INT,
	Name NVARCHAR(15),
	League NVARCHAR(15),
	Title NVARCHAR(15),
	Honor INT,
	Prestige INT,
	Cities INT,
	Tributary INT,
	Population INT
)
AS
BEGIN
	DECLARE @L INT = @IdRank / 10;
	INSERT @Return
	SELECT U.Id,U.RankGeneral,U.Name,L.Name,T.Name,U.Honor,U.Prestige,Count(C.Id),Count(CT.Id),Sum(C.Population)
	FROM Leagues AS L, Titles AS T, Cities AS C, Cities AS CT,
	(SELECT * FROM Users WHERE RankGeneral >= @L * 10 AND RankGeneral <= (@L * 10) + 10) AS U
	WHERE L.Id = U.IdLeague AND T.Id = U.IdTitle AND C.IdOwner = U.Id AND CT.IdSovereign = C.Id
        GROUP BY U.Id,U.RankGeneral,U.Name,L.Name,T.Name,U.Honor,U.Prestige
	RETURN
END

Adicionei apenas  o GROUP BY nas colunas que estavam no seu SELECT e que não continham funções COUNT ou SUM.

O Count(C.*) não funciona, tens que usar um campo específico como fez ou um Count(*)

Espero ter ajudado!

Valeu!

---

Se a resposta for relevante ou tenha resolvido seu problema, marque como útil/resposta!

André Secco
Microsoft MSP & MSDN Tech Advisor
Blog: http://andresecco.com.br
GitHub: http://github.com/andreluizsecco
Twitter: @andre_secco

Answer 2

Voce nao pode fazer ,Count(C.Id),Count(CT.Id),Sum(C.Population) sem agrupar (group by) os campos U.Id,U.RankGeneral,U.Name,L.Name,T.Name,U.Honor,U.Prestige

o correto (sintaxe) seria 

SELECT U.Id,U.RankGeneral,U.Name,L.Name,T.Name,U.Honor,U.Prestige,Count(C.Id),Count(CT.Id),Sum(C.Population)
	FROM Leagues AS L, Titles AS T, Cities AS C, Cities AS CT,
	(SELECT * FROM Users WHERE RankGeneral >= @L * 10 AND RankGeneral <= (@L * 10) + 10) AS U
	WHERE L.Id = U.IdLeague AND T.Id = U.IdTitle AND C.IdOwner = U.Id AND CT.IdSovereign = C.Id
Group By U.Id,U.RankGeneral,U.Name,L.Name,T.Name,U.Honor,U.Prestige

Mas sinceramente eu nao entendo o que voce quer fazer com isso, pois nao faz sentido a soma da populaçao.

Se voce fizer somente 

SELECT U.Id,U.RankGeneral,U.Name,L.Name,T.Name,U.Honor,U.Prestige,1,1,C.Population
	FROM Leagues AS L, Titles AS T, Cities AS C, Cities AS CT,
	(SELECT * FROM Users WHERE RankGeneral >= @L * 10 AND RankGeneral <= (@L * 10) + 10) AS U
	WHERE L.Id = U.IdLeague AND T.Id = U.IdTitle AND C.IdOwner = U.Id AND CT.IdSovereign = C.Id

voce vai obter o mesmo resultado, a nao ser que um usuario possua mais de uma cidade.

att

---

William John Adam Trindade
Analyste-programmeur
----------------------------------------------------------

Sogi Informatique ltée

Answer 3

Deleted

Answer 4

Sim cada usuário pode ter mais de uma cidade, o intuito era mostrar a soma total de todas as cidades, obrigado ^^

Retorna a quantidade total de cidades, a quantidade total de cidades tribunarias e a soma total da população de todas as cidades. obrigado pela resposta ^^

Parece muito mais limpo mesmo, mas ainda não entendo profundamente a utilidade de JOIN por isso não uso.