Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)

Question

I have this query, but when I run the code, I get the error message.

Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)

Can anybody help me out?

The query follows:

SELECT
  obv.POS, 
  obv.CODVIDRO, 
  obv.PRENATW,
  obv.FASE, 
  obv.LARGURA,
  obv.ALTURA, 
  obv.QTDE,
  obv.OBS, 
  obv.AREAEMP, 
  obv.PESO,
  obv.CUSTO, 
  obv.CUSTOIPI, 
  obv.CODMATRIZ,
  IIF( obv.CODMATRIZ > '', obv.CODMATRIZ, obv.CODVIDRO ) as CodVidro2,                                                    
  vid.REF, 
  vid.DESCRICAO, 
  vid.PRENATV, 
  vid.ESPESSURA,
  obi.Subdiv, 
  obi.CodEsqd, 
  obi.Tipo, 
  obi.Obs, 
  obi.CodOracle, 
  obi.Localiz,
  IIF( pnw.Descricao > '', pnw.Descricao, obv.PRENATW ) as pnwDescricao,
  SUM(((OBV.LARGURA/1000)*(OBV.ALTURA/1000)*OBV.QTDE)) AS AREA,
  IIF(obv.CODVIDRO = 'V-ARAM-05',(SUM((((OBV.LARGURA/1000)*(OBV.ALTURA/1000)*OBV.QTDE))/3.57)), IIF(obv.CODVIDRO = 'V-MINB-04',(SUM((((OBV.LARGURA/1000)*(OBV.ALTURA/1000)*OBV.QTDE))/3.57)), (SUM((((OBV.LARGURA/1000)*(OBV.ALTURA/1000)*OBV.QTDE))/7.062)))) AS QTDECHAPAUNIT
FROM ObCalcV AS obv
LEFT JOIN Vidros vid ON (obv.CodVidro=vid.CodVidro)
LEFT JOIN ObItens obi ON (obv.ID_Obra=obi.ID_Obra AND obv.ItemObra=obi.ItemObra)
LEFT JOIN PreNat_W pnw ON (obv.PRENATW=pnw.PRENATW)
WHERE (obv.ID_OBRA = :ID_OBRA)
GROUP by obv.POS, 
  obv.CODVIDRO, 
  obv.PRENATW,
  obv.FASE, 
  obv.LARGURA,
  obv.ALTURA, 
  obv.QTDE,
  obv.OBS, 
  obv.AREAEMP, 
  obv.PESO,
  obv.CUSTO, 
  obv.CUSTOIPI, 
  obv.CODMATRIZ,                                                        
  vid.REF, 
  vid.DESCRICAO, 
  vid.PRENATV, 
  vid.ESPESSURA,
  obi.Subdiv, 
  obi.CodEsqd, 
  obi.Tipo, 
  obi.Obs, 
  obi.CodOracle, 
  obi.Localiz
ORDER BY CodVidro2, obv.PRENATW, obi.Tipo

The full error follows:

can't format message 13:896 -- message file C:\Windows\firebird.msg not found. Dynamic SQL Error. SQL error code = -104. Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

At a guess - no time to check - you will need to add the pnw.Descricao to the group by as well. — Mark Rotteveel
Possible duplicate of Error: Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause) — Mark Rotteveel

Arioch 'The Arioch 'The · Accepted Answer · 2018-12-17T15:08:07

This is quite easy to detect. You are a programmer afterall.

https://docs.google.com/spreadsheets/d/1tBfebBKRIumJ0I3BaN2gNwGIMivc1wDJ-AVeohH0DCc/edit?usp=sharing

You start a spreadsheet. Microsoft Excel, OpenOffice, Gnumeric, MyOffice, Google Spreasheet - just any. You need very basic functions.
You copy your select DB column names into one spreadsheet column and group by column names into another row.

2.1. There above they turned to be one cell after copying them from Stack Overflow into OpenOffice, so I had to use "Split data by columns" function and thus for me they ended in rows rather than column. It is less comfortable to read, but does not change much.

2.2. Notice, you do not need column formulas, all those expressions, you need column names.

2.3. This methodic is very obvious and very simple, for queries like Select aaa, x.aaa, tablename.aaa from tablename as x it would see three different columns. Well, it would help you to make all your columns referenced uniformly in your query.

you then sort both your spreadsheet columns (or in my case I sorted rows) individually by alphabet.
now you insert simple formula in the column between those columns, like {B1} =A1=C1 and copy it throughout the column (or row).
Now you just go down that column and check where "True" was changed into "False".

Few cut-move-paste operations and restorations of that formula - and you would have all the out-of-sync columns spotted.

In your case - and you can check it by the link - the first column out-of-sync was CodVidro2 - however it was missed from Group By so it did not matter. Thus you shift the "Grop by columns" further one cell to restore synchronization and go down the line spotting next place TRUE becomes FALSE.

That would be obv.POS DB column that you seems to be missing.

There later will be two more columns out-of-sync, but again they would be not part of Group by so most probably wouldn't matter.

Notice, this method is ultra easy and ultra simple, it would not spot the columns mentioned inside expressions (aggregate function included) but not listed explicitly in the select list - unless you would extract them from those expressions. However for those less simple cases you can do the extraction it by hands. And for 90% of cases the spreadsheet - any spreadsheet - would readily do the comparison for you.

Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)

1 Answers