GROUP BY in SQLServer does not work the same as in MySQL

Question

GROUP BY in SQLServer does not work the same as in MySQL

Navigation

#1 by (0 votes)
#2 by (-1 votes)

0

Let me explain: When I make a GROUP BY with MySQL, I can select fields that can not be grouped, as descriptions (varchars), and the result shows, of all the grouped values, the first one that I find.

But now I find SQL Server and it turns out that the same group by fails, depending on the reason, because it "does not know" what to do with those fields if a grouping function has not been applied to them, such as SUM, AVG, COUNT ...

An example:

I have the following table:

ID  GRUPO   VALOR   DESCRIPCION
--------------------------------
1   1       10      Prueba1
2   1       20      Prueba2
3   2       10      Prueba3
4   2       20      Prueba4

And I execute the following select:

SELECT GRUPO, AVG(VALOR), DESCRIPCION FROM 'GroupBy' group by GRUPO;

With this select, in MySQL I get:

GRUPO   AVG(VALOR)  DESCRIPCION
-------------------------------
1       15.0        Prueba1
2       15.0        Prueba3

But in SQL Server I get:

SQL Error 8120: La columna "DESCRIPCION" de la lista de selección no es válida, porque no está contenida en una función de agregado ni en la cláusula GROUP BY.

Is there a way to make a GROUP BY in SQL Server that returns the same result that it would give me in MySQL?

mysql sql sql-server

asked by Alberto Torre 27.04.2018 в 00:29

source

2 answers

Show JSON in a table [duplicated] To use Payload Claim Sub and Aud on a system with Json Web Token

score 0 · Answer 1

You can try the following code, for your description;

SELECT GRUPO, AVG(VALOR) AS 'AVG', MIN(DESCRIPCION) AS 'DESCRIPCION' 
FROM [group by] group by GRUPO

If I wanted to by a more recent date it would be something like:

SELECT a.GRUPO,a.[AVG Valor],b.Descripcion,b.Precio,a.Fecha  FROM (SELECT
GRUPO, AVG(VALOR) AS [AVG Valor], max(Fecha) AS fecha FROM [group  by] GROUP BY Grupo) a INNER JOIN [group by] b 
ON a.Fecha=b.fecha and  a.GRUPO=b.GRUPO

score -1 · Answer 2

You only need to add the field Descripcion , you are requesting to include a group and description but only grouping group, try the following:

SELECT GRUPO, AVG(VALOR), DESCRIPCION FROM 'GroupBy' group by GRUPO, DESCRIPCION;