Having count(distinct x) not returning correct results - Teradata SQL

Question

I'm working in Teradata, and my data looks like this:

Image of data my query is:

select id, type
from table1
where id in (100, 101)
group by 1,2
having count(distinct type) > 1

but its returning nothing??? I'm confused why it's not working. I'm expecting it to return where the distinct type is greater than 1, so shouldn't the output be???:

Image of expected output

Please help

Answer 1

select distinct id, type
from table1
where id in (100, 101)

Answer 2

One option is to first find ID value(s) that satisfy the condition, and then - using that query as a subquery - extract other columns you're interested in. As you want only one comment per [ID, TYPE] combination, use one of aggregation functions, such as MIN or MAX.

SQL> select id, type, min(comments) comments
  2  from table1
  3  where id in (select id
  4               from table1
  5               where id in (100, 101)
  6               group by id
  7               having count(distinct type) > 1
  8              )
  9  group by id, type;

        ID TYPE   COMMENTS
---------- ------ ---------------
       100 Honda  This is a large
       100 Toyota Yr 1990

SQL>