Error when grouping table field

Question

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

When I use the 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 follows 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

Answer 1

Your mistake is because you are trying to group only one field in your Quey, you would have to use the MAX, MIN, AVG functions a simply 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