Subquery is not returning a count value from a different table

Hello I am new to postgresql and have been stuck on the following problem for awhile. Below I have two tables: Table 1: avatar

  login   |    name     | gender |    race    
----------+-------------+--------+------------
 agnetha  | Flutter Shy | F      | faerie
 agnetha  | Flit        | F      | faerie
 agnetha  | Whisp       | F      | faerie
 astral   | Lightning   | F      | raccoon
 astral   | Elphaba     | F      | wizard
 bobby    | Steve       | M      | wizard
 bobby    | Wayne       | M      | footballer
 bobby    | Smiley      | M      | shark
 ....

Table 2: player

  login   |  name   | gender |     address     |       joined        
----------+---------+--------+-----------------+---------------------
 agnetha  | Sarah   | F      | 191 Howth Ave   | 2016-08-01 17:50:44
 astral   | Jessie  | F      | 81 Franklin St  | 2016-05-28 21:21:21
 bobby    | Robert  | M      | 7 Avenue Rd     | 2016-07-19 12:00:59
 cait     | Caitlin | F      | 444 Sycamore Dr | 2016-08-05 23:51:17
 carter   | Lucy    | F      | 213 John Ave    | 2016-07-08 19:28:46
 cash     | Thomas  | M      | 213 John Ave    | 2016-07-04 15:37:59
....

My task is to use both tables and find out which logins changed their gender in the avatar table and display the sum of "avatars" an individual has. Example agnetha has 3 avatars, astral has 2, bobby has 3.

My issue is that when I try to count using a subquery, postgresql says that more than one row returned by a subquery used as an expression

This is what I've written as my query so far

SELECT player.login,player.name,player.gender, 
(SELECT count(avatar.login) FROM avatar group by login order by login)as avatars 
FROM player INNER JOIN avatar ON player.login = avatar.login AND avatar.gender <> player.gender 
GROUP BY avatar.login, player.login ORDER by login;

Any idea how I can get the count of all the avatars per login and display them on a table that looks like this:

  login  |  name   | gender | avatars 
---------+---------+--------+---------
 carter  | Lucy    | F      |       3
 coder   | Jeff    | M      |       3
 dazzle  | Alice   | F      |       3

Solution

Hmm . . . I think you want aggregation with filtering:

select p.login, p.name, p.gender, count(*)
from player p join
     avatar a
     on p.login = a.login
group by p.login, p.name, p.gender
having count(*) filter (where a.gender <> p.gender) > 0;

The count(*) counts all the avatars. The having clause checks counts the number the genders that are different, requiring that the logins have at least one mismatch.