Group by with SQL Server error

Asked

Viewed 6,395 times

1

Good morning, I would like to know why my SQL query is not working, I am using SQL Server 2008 and my GROUP BY is going wrong. I have checked and the table names are correct and are filled in correctly.

Code: 

SELECT categoria, nome_categoria
FROM tabela_teste.dbo.teste
WHERE vigencia = '2016-04'
GROUP BY categoria
ORDER BY nome_categoria;

Error:

is invalid in the select list because it is not contained in either an Aggregate Function or the GROUP BY clause.

sql sql-server

  • If you run the command select category from tabela_test.dbo.test Where vigencia = '2016-04' group by category order by name_category; works?

  • If it runs this way it still doesn’t work, the group by error continues. But if I remove the table name and the order by it works. However I need to use group by and order by for my research to work.

  • Post the table structure. I answered a question here, see if it helps: http://answall.com/questions/131637/buscar-somente-o-menor-n%C3%Bamero-de-every-letter/131640#131640

  • Looking at some sites all use a group by followed by a group functions(sum, max...), in my case I do not need the group function. But as I said without the order by the command works, my table is filled correctly otherwise the command would not have worked, I came to think that group by works only with grouping functions.

  • I looked at the link you sent me, grateful as I said above there you use the grouping function, try to put order by on your command and see if it works correctly.

1 answer

8

All select fields must be grouped or contained in the GROUP BY clause or grouped by SUM, MIN, MAX

 select categoria, nome_categoria from tabela_teste.dbo.teste 
 where vigencia =   '2016-04' group by categoria, nome_categoria 
 order by nome_categoria;

Below some selects that are syntactically correct:

select cdfilial, cdlocal, dslocal  from minhatabela group by cdfilial, cdlocal, dslocal

select min( cdfilial ), cdlocal, dslocal  from minhatabela group by cdlocal, dslocal

select min( cdfilial ), max( cdlocal ), min( dslocal )  from minhatabela

select sum( cdfilial ), max( cdlocal ), min( dslocal ) from minhatabela

You will determine how you want to group your data and what type of grouping you want.

  • Could be more specific?

  • This feature is valid only for sqlServer 2008?

  • No. This works on every bank I know. Sqlserver, Oracle, Firebird, Sqlite, Mysql.

  • I changed my code here with your suggestion and it worked, thanks for the help. I wonder if this feature persists in other versions of sqlServer?

  • Yes. All the versions.

  • 2

    @Fernando This is the SQL standard and is supported by all databases. Reginaldo, Mysql, in addition to supporting the default, also accepts this pyre of leaving fields outside the group by and also without any aggregation function, hence it decides what to do. I paritcularmente consider this more a bug than a Feature, because you may have left field out by mistake and it does not warn you - simply gives the result that you did not expect.

  • @Caffé I tested in Mysql without group by in one of the fields and it simply grouped by the others and ignored the one that was not contained. Sometimes this is also welcome. Anyway you have to stay tuned.

Show 2 more comments

Browser other questions tagged sql sql-server

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