Error in group by of a query in db2 SQL

0

Query:

Select ITEMNFS.Recnum, ITEMNFS.ITEM, sum(ITEMNFS.VL_TOTAL), sum(ITEMNFS.QTDE_FATUR), ITEM.PER_IPI 
from ITEMNFS 
inner join ITEM on ITEMNFS.ITEM = ITEM.ITEM inner join NFS on ((ITEMNFS.NRO_NFS = NFS.NRO_NFS) and (ITEMNFS.SERIE = NFS.SERIE)) 
where (NFS.DT_EMISSAO between '2017-12-01' and '2018-02-20') 
group by ITEMNFS.ITEM

The error is as follows:

  

SQL0119N An expression beginning with "PER_IPI" specified in a   SELECT or HAVING clause was not specified in the GROUP BY clause or   is in a SELECT, HAVING, or ORDER BY clause with a   column without a specified GROUP BY clause. SQLSTATE = 42803

    
asked by anonymous 20.02.2018 / 13:25

1 answer

1

The error says that you need to add all clauses that are not sum() to group by . Try this:

Select ITEMNFS.Recnum, ITEMNFS.ITEM, sum(ITEMNFS.VL_TOTAL), sum(ITEMNFS.QTDE_FATUR), ITEM.PER_IPI 
from ITEMNFS 
inner join ITEM on ITEMNFS.ITEM = ITEM.ITEM inner join NFS on ((ITEMNFS.NRO_NFS = NFS.NRO_NFS) and (ITEMNFS.SERIE = NFS.SERIE)) 
where (NFS.DT_EMISSAO between '2017-12-01' and '2018-02-20') 
group by ITEMNFS.Recnum, ITEMNFS.ITEM, ITEM.PER_IPI 

You can read in this link about group by , there are several examples of selects with many columns.

    
20.02.2018 / 13:28