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

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

Why are you doing a group by? Can there be multiple status for a combination of CREDAT,MESTYP,DIRECT,RCVPRN,SNDPRN? — DhruvJoshi, May 05 '22 at 08:47

score 0 · Answer 1 · answered May 10 '22 at 12:55

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

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

1 Answers1