Why does COUNT count incorrectly?

Question

SQL Server

PeopleTable (около 500 000 строк)
id | name | gender -- "М" /  "Ж" / NULL
ListTable (около 10 000 000 строк)
id | userID | date

Count the number of rows in the ListTable where the userID belongs to men/women.
Tried:

SELECT COUNT(list.id) AS men 
  FROM ListTable AS list 
    LEFT JOIN PeopleTable AS people ON (list.userID = people.id) 
  WHERE people.gender = 'М'". 
  ... 
  WHERE people.gender = 'Ж'

But the sum is more than the number of rows in the ListTable by about 15%.

Answer 1

Try:

SELECT 
  COUNT(DISTINCT CASE WHEN people.gender = 'M' THEN list.id END) AS mens,
  COUNT(DISTINCT CASE WHEN people.gender = 'F' THEN list.id END) AS womens,
  COUNT(DISTINCT CASE WHEN people.gender IS NULL THEN list.id END) AS unknown
FROM ListTable AS list 
LEFT JOIN PeopleTable AS people ON (list.userID = people.id) 
;

MS SQL Fiddle

Answer 2

LEFT JOIN. This means that in both cases you take into account rows from the ListTable for which there is no corresponding entry in the PeopleTable.