Hive Expression not in GROUP BY key 'pat_dtl_start_dt' (state=42000,code=10025)

0
votes

SQL Code: 

CREATE TABLE dev.new STORED AS orc tblproperties("orc.compress" = "SNAPPY") AS
SELECT pat_dtl_start_dt, individual_id, bdy_lctn_cd, prcdr_cd, trtmnt_cd, SUM(allw_amt)
FROM dev.old
GROUP BY pat_dtl_start_dt, individual_id, bdy_lctn_cd, prcdr_cd, trtmnt_cd;

Error Message:

Error: Error while compiling statement: FAILED: SemanticException [Error 10025]: Line 2:7 Expression not in GROUP BY key 'pat_dtl_start_dt' (state=42000,code=10025)

I think I already include all selected columns in the group by key. Don't know why this still happen.

1
sqlgroup-byhiveql

1 Answers

0
votes

Your GROUP BY looks correct . . . enough. I don't see why it is causing that error.

However, you probably don't want allw_amt in it. I am guessing that this does what you want:

SELECT pat_dtl_start_dt, individual_id, bdy_lctn_cd, prcdr_cd, trtmnt_cd,
       SUM(allw_amt)
FROM dev.old
GROUP BY pat_dtl_start_dt, individual_id, bdy_lctn_cd, prcdr_cd, trtmnt_cd;