Right way to filter inside a having clause

I’m working on a weird query that might need a filter inside a having clause but since I’m not including the column that I’m trying to filter in the having clause in the group by it does not work. Wondering if it can be done in any other way in the sql itself? TIA

For clarification on what I’m trying to do: I need to get the table_id
and coffee_number for locations where only ‘BLACK’ is served but no
‘LATTE’. If both are being served then it should not be returned. From
the data in the tables, location G should not be returned as its only
serving LATTE location H should not be returned as its serviceng both
LATTE and BLACK. location D should be returned as its serving only
BLACK.

I have put everything in the SQL Fiddle here

In the results, I want the row to not show up

|    44444 |            12 |        G | 

Schema Setup:

create table some_table_test(coffee_id integer, coffee_number integer, coffee_type text);
create table some_table_test_coffee(coffee_id integer, location text);
create table some_table_number(coffee_number integer, table_id integer);

insert into some_table_test (coffee_id, coffee_number, coffee_type)
values (1, 10, 'BLACK'),
       (2, 10, 'BLACK'),
       (3, 11, 'BLACK'),
       (6, 12, 'BLACK'),
       (4, 11, 'LATTE'),
       (7, 12, 'LATTE');

insert into some_table_test_coffee (coffee_id, location)
values (1, 'D'),
       (3, 'H'),
       (2, 'D'),
       (3, 'D'),
       (4, 'H'),
       (7, 'G');

insert into some_table_number (coffee_number, table_id)
values (10, 123456),
       (11, 98764),
       (12, 44444);

Query:

select stn.table_id, st.coffee_number, location
from some_table_test st
         join some_table_test_coffee s on st.coffee_id = s.coffee_id
join some_table_number stn on stn.coffee_number = st.coffee_number
group by location, st.coffee_number, stn.table_id
having count(distinct coffee_type) = 1 -- I need a way to say coffee_type != 'LATTE'

Results:

| table_id | coffee_number | location |
|----------|---------------|----------|
|   123456 |            10 |        D |
|    98764 |            11 |        D |
|    44444 |            12 |        G |

>Solution :

While not explicitly stated, I read the requirements as "rows that have coffee types other than Lattes".
Once formulated like this, you can translate it into a case expression and count it:

select stn.table_id, st.coffee_number, location
from some_table_test st
         join some_table_test_coffee s on st.coffee_id = s.coffee_id
join some_table_number stn on stn.coffee_number = st.coffee_number
group by location, st.coffee_number, stn.table_id
having COUNT(CASE coffee_type WHEN 'LATTE' THEN NULL ELSE 1 END) > 0

SQLFiddle demo