SQL: STATUS column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [duplicate]

Question

Hi Just want to GROUP BY my table without using the status column. but I keep getting the error and I can't figure out to resolve it.

SELECT * FROM ( SELECT COUNT(DOCNUM) 

AS 
count_idoc, 

CREDAT,
[STATUS],
MESTYP,
DIRECT,
RCVPRN,
SNDPRN,
 
 (CASE 
    WHEN [STATUS] = '03' THEN 'IDOC Sent'
    WHEN [STATUS] = '53' THEN 'IDOC Received'
    WHEN [STATUS] = '30' THEN 'Waiting to be processed'
    WHEN [STATUS] = '64' THEN 'Waiting to be processed'
    WHEN [STATUS] > '42' THEN 'IDOC not posted'
    WHEN [STATUS] < '42' THEN 'IDOC not sent'
 END) AS 'status_msg'

 FROM [MIA-Time]

 GROUP BY CREDAT,MESTYP,DIRECT,RCVPRN,SNDPRN
 ) status_result

Answer 1

Thank you @JamesS for your valuable comment, posting it as the answer. As JamesS mentioned in the comments, column [STATUS] is missing in your group by list.

All the columns which are not part of the aggregate should be included in the group by list.

SELECT * FROM 
  ( SELECT COUNT(DOCNUM) AS count_idoc, 
       CREDAT,
       [STATUS],
       MESTYP,
       DIRECT,
       RCVPRN,
       SNDPRN,
       (CASE 
          WHEN [STATUS] = '03' THEN 'IDOC Sent'
          WHEN [STATUS] = '53' THEN 'IDOC Received'
          WHEN [STATUS] = '30' THEN 'Waiting to be processed'
          WHEN [STATUS] = '64' THEN 'Waiting to be processed'
          WHEN [STATUS] > '42' THEN 'IDOC not posted'
          WHEN [STATUS] < '42' THEN 'IDOC not sent'
       END) AS 'status_msg'
  FROM [MIA-Time] 
  GROUP BY CREDAT,[STATUS],MESTYP,DIRECT,RCVPRN,SNDPRN
) status_result