0
Hive vertion:apache-hive-2.3.0
hive> select TAG_EMOTION_KEY AS key,count(1) AS cnn from NB_ORDER_RATE_EMOTION t  where t.cnn>10 group by TAG_EMOTION_KEY ;
FAILED: SemanticException [Error 10002]: Line 1:84 Invalid column reference 'cnn'

what's the reason?

BruceWayne
  • 3,286
  • 4
  • 25
  • 35
Xin.Jing
  • 9
  • 2

1 Answers1

0

You are basically trying to get list of TAG_EMOTION_KEY which has appeared more then 10. For that you need to use HAVING instead of WHERE clause.

Like:

select
    TAG_EMOTION_KEY AS key,
    count(1) AS cnn
from
    NB_ORDER_RATE_EMOTION t
group by
    TAG_EMOTION_KEY
having
    count(1) > 10
;

Following question provides the difference between HAVING & WHERE clause: SQL - having VS where

Ambrish
  • 3,627
  • 2
  • 27
  • 42
  • Tks, i want to know does HIVE support Alias? For example: "select TAG_EMOTION_KEY AS key,count(1) from NB_ORDER_RATE_EMOTION t group by t.key" -----It does'nt work. – Xin.Jing Aug 24 '17 at 02:14