I'm trying to do the following SELECT:
SELECT P.Nome,P.TipoId,P.QuantidadeMensal FROM StockPhotos.Pacote P GROUP BY P.QuantidadeMensal;
However, I'm getting this error message:
Message 8120, Level 16, State 1, Line 2
Column 'StockPhotos.Page.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I'm not understanding what the problem is, since Nome is being selected.
SQL is saying that the StockPhotos.Pacote.name column should:
SUM or COUNT , or GROUP BY . Basically, when we use GROUP BY all the fields used in SELECT should either be used in aggregate functions or should also be part of GROUP BY .
In your case, just to illustrate , you could use this way (two fields with count, and one in group by):
SELECT COUNT(P.Nome), COUNT(P.TipoId) ,P.QuantidadeMensal
FROM StockPhotos.Pacote P
GROUP BY P.QuantidadeMensal;
or so (all fields in group by):
SELECT P.Nome,P.TipoId,P.QuantidadeMensal
FROM StockPhotos.Pacote P
GROUP BY P.Nome,P.TipoId,P.QuantidadeMensal, P.QuantidadeMensal;
These are just examples, regardless of the outcome. See that not only P.Name, but also P.TipoId must meet the same criteria.
In free translation the error says:
The 'StockPhotos.Pacote.Name' column is invalid in the selection list because it is not contained in a clustered function or in the
GROUP BYclause.
That is, the column has to be in GROUP BY or in a clustered function ( COUNT , AVG , SUM , MIN , etc).
You need to indicate what you want to do with this column at SQL Server .
Any field that returns in the query without functions like SUM, AVG, etc must be in group by
Example:
SELECT
P.Nome,P.TipoId,SUM(P.QuantidadeMensal) AS total
FROM
StockPhotos.Pacote P
GROUP BY
P.Nome,P.TipoId