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 = 'peter' 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
