Error when grouping table field

Question

Error when grouping table field

Navigation

#1 by (1 votes)

2

I need to group the data of a SELECT by the a.afo_vch_NmrCtf field, because the data is being returned as follows:

When I use GROUP BY the following error is returned:

Msg 8120, Level 16, State 1, Line 2
Column 'tbl_Eqp.eqp_vch_NmrCnt' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Below is my SELECT :

SELECT 
     e.eqp_vch_NmrCnt AS [Contrato]
    ,e.eqp_itg_NmrSerie AS [Serie]
    ,f.fxa_tyi_Faixa AS [Faixa]
    ,a.afo_vch_NmrCtf AS [Certificado]
    ,CASE 
            WHEN a.afo_dtt_DataTrm < GETDATE() THEN 'Vencida'
            WHEN DATEDIFF(DAY, a.afo_dtt_DataTrm, GETDATE()) < 90 THEN 'OK'
            ELSE 'Vence em ' + CONVERT(VARCHAR, DATEDIFF(DAY, a.afo_dtt_DataTrm, GETDATE())) + ' dias'
    END AS [Status]
FROM
    tbl_Eqp e 
    INNER JOIN tbl_Faixa f ON f.eqp_itg_ID = e.eqp_itg_ID
    INNER JOIN tbl_AxlFaixaOcn a ON a.eqp_itg_ID = e.eqp_itg_ID
GROUP BY 
    a.afo_vch_NmrCtf

sql sql-server group-by

asked by anonymous 22.09.2016 / 15:30

1 answer

Working with Dates in JavaWeb How to check if a (variable) column exists inside a 'data.frame'?

score 1 · Accepted Answer

Your error is because you are trying to group only one field in your quey, you would have to use the MAX, MIN, AVG functions simply to group all your query fields.

SELECT 
     e.eqp_vch_NmrCnt AS [Contrato]
    ,e.eqp_itg_NmrSerie AS [Serie]
    ,f.fxa_tyi_Faixa AS [Faixa]
    ,a.afo_vch_NmrCtf AS [Certificado]
    ,CASE 
            WHEN a.afo_dtt_DataTrm < GETDATE() THEN 'Vencida'
            WHEN DATEDIFF(DAY, a.afo_dtt_DataTrm, GETDATE()) < 90 THEN 'OK'
            ELSE 'Vence em ' + CONVERT(VARCHAR, DATEDIFF(DAY, a.afo_dtt_DataTrm, GETDATE())) + ' dias'
    END AS [Status]
FROM
    tbl_Eqp e 
    INNER JOIN tbl_Faixa f ON f.eqp_itg_ID = e.eqp_itg_ID
    INNER JOIN tbl_AxlFaixaOcn a ON a.eqp_itg_ID = e.eqp_itg_ID
GROUP BY 
    e.eqp_vch_NmrCnt
    ,e.eqp_itg_NmrSerie
    ,f.fxa_tyi_Faixa
    ,a.afo_vch_NmrCtf
    ,a.afo_dtt_DataAfr
    ,a.afo_dtt_DataTrm