Error in a simple SELECT with Group By - SQL Server

Asked

Viewed 4,418 times

5

I’m trying to do the following SELECT:

SELECT P.Nome,P.TipoId,P.QuantidadeMensal FROM StockPhotos.Pacote P GROUP BY P.QuantidadeMensal;

But I’m getting this error message:

Message 8120, Level 16, Status 1, Line 2

Column 'Stockphotos.Pack.Name' is invalid in the select list because it is not contained in either an Aggregate Function or the GROUP BY clause.

I don’t understand what the problem is, because Nome is being selected.

sql database sql-server
  • 1

    What result do you want? I couldn’t really understand, at first, the SQL server is grouping only by Quantityand therefore, once it groups by monthly amount, it doesn’t know what to do with Tipoid and Name because they are not in an aggregation function ( I didn’t understand exactly what you need too, so I can only point out the error) .

3 answers

8


SQL is saying that the column Stockphotos.Pack.Name should:

  1. Being computed with an aggregation function, for example SUM or COUNT, or
  2. It must be part of the grouping, that is, it must be in the GROUP BY.

Basically, when we use GROUP BY all the fields used in SELECT must, or be used in aggregation functions, or must also be part of the GROUP BY.

In your case, just to illustrate, could use so (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 result. Note that not only P.Name, but also P.Tipoid should meet the same criteria.

  • 1

    Perfect, in addition to the explanation solved a problem I had trying to group by Nome, Ricardo, your answer was Punctual (rsrs) !!

3

In free translation the error says:

The 'Stockphotos.Pack.Name' column is invalid in the selection list because it is neither contained in a grouped function nor in the clause GROUP BY.

I mean, the spine has to be in the GROUP BY or in a group function (COUNT, AVG, SUM, MIN, etc.).

You need to indicate what you want to do with this column to SQL Server.

  • Explained very well, thanks by the way... +1

3

Every field that returns in the query without functions like SUM, AVG, etc must be in the group by

Example:

SELECT 
 P.Nome,P.TipoId,SUM(P.QuantidadeMensal) AS total 
FROM 
 StockPhotos.Pacote P 
GROUP BY 
  P.Nome,P.TipoId

  • Thanks for the explanation ! note 10 +1

Browser other questions tagged sql database sql-server

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