Struggling with a multiple count statement in SQL

I've been struggling with a multiple count statement. my table is build like this;

person1, person2, relation
peter     ann      coworkers

I need to count how many relations peter has under coworker, under lovers . etc etc

I've come up with this;

select(
select count(*)
from rel
where person1 = 'pet开发者_如何学编程er' and relation = 'coworker'
)as PetersFriends,(
select count(*)
from rel
where person1 = 'peter' and relation = 'lovers'
)
as PetersLovers
;

but I can't seem to get it to work.

select relation, count(*) as RelationCount
from rel
where person1='peter'
group by relation

Your original query seems to want a pivoted result. If this is the case you would need something like.

select person1,
       COUNT(case when relation = 'coworker' then 1 end) AS Friends,
       COUNT(case when relation = 'lovers' then 1 end) AS Lovers
from rel
where person1 IN ('peter','ann','wendy')
GROUP BY person1

Assuming that

Peter, Ann, Coworker

Will also have a corresponding entry:

Ann, Peter, Coworker

Then you can simply do this:

SELECT Person1, Relation, COUNT(1)
FROM Rel
GROUP BY Person1, Relation

Otherwise, you can do this:

SELECT Person1, Relation, COUNT(1)
FROM Rel
GROUP BY Person1, Relation
UNION 
SELECT Person2, Relation, COUNT(1)
FROM Rel
GROUP BY Person2, Relation