How to have 'distinct' in having clause

Question

EDIT: This is an example relation! I need it to work on a bigger relation so no workarounds!

So I was given a simple task and at first I didn't see what could possibly be wrong and now I just don't understand why it doesnt work.

Lets say I have a table of people and their friends and I want to select the ones who have 2 or more friends.

people

------------------------------
|person  | friend | relation |
|-----------------------------
|ana     | jon    | friend   |
|ana     | jon    | lover    |
|ana     | phillip| friend   |
|ana     | kiki   | friend   |
|mary    | jannet | friend   |
|mary    | jannet | lover    |
|peter   | july   | friend   |

---

I would want to do a

 SELECT person FROM people GROUP BY person HAVING count(distinct friend) > 1;

and get

-------
| ana |
-------

But I get a syntax error when using the 'distinct' in the HAVING clause. I understand that the 'distinct' is a part of the projection clause but how do I make 'count' only count distinct entries without an additional subquery or something?

EDIT: The best I could come up with is:

SELECT tmp.person FROM (SELECT person, count(distinct friend) 
             AS numfriends FROM people GROUP BY person) AS tmp 
       WHERE tmp.numfriends > 1;

Solution 1

From the doc

http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_0162.htm

The condition in the HAVING clause cannot include a DISTINCT or UNIQUE aggregate expression.

An work-around would be to have the count distinct in the select

SELECT 
person,
count(distinct friend) as f_count
FROM people 
GROUP BY person 
HAVING f_count > 1;

UPDATE :

Checked the document and found the facts

The HAVING clause is evaluated before the SELECT - so the server doesn't yet know about that alias.

So to achieve the goal it could be done as

select
person,
f_count
from(
 SELECT 
 person,
 count(distinct friend) as f_count
 FROM people
 GROUP BY person 
)x
where f_count > 1 

Solution 2

you need to write it like this

SELECT person
FROM people
WHERE relation = 'friend'
GROUP BY person 
HAVING count(*) > 1;