Return data when not all values in column are equal

user_5491428 0
Viewed 27

I have a simple table which looks like this:

| yyyy_mm_dd | id | name     | count |
|------------|----|----------|-------|
| 2020-10-31 | 1  | price    | 90    |
| 2020-10-31 | 1  | restrict | 90    |
| 2020-10-31 | 1  | checkin  | 90    |
| 2020-10-31 | 2  | price    | 322   |
| 2020-10-31 | 2  | restrict | 322   |
| 2020-10-31 | 2  | checkin  | 311   |
...

How can I construct a query that will return the ids that do not have the same count for each name? So using the above data, id 2 would be returned as not all name counts are equal.

It would be great if it's possible without hardcoding each name individually as there are a lot in the real dataset.

hiveql hive sql
1 Answers

A simple method is aggregation:

select name
from t
group by name
having min(count) <> max(count);

If you want to be sure there are three rows, then add and count(*) = 3.

user_11440350
Related