Почему COUNT неправильно считает?

Question

[Depleted]

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

Посчитать количество строк в ListTable, где userID принадлежит мужчинам/женщинам.
Пробовал:

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

Но сумма больше, чем количество строк в таблице ListTable примерно на 15%.

Answer 1

[Slava Rozhnev]

Попробуйте:

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

Comments

[Depleted]

Спасибо большое!

Данный запрос крашит хост, хотя по одному подсчету всё считает просто великолепно, хоть и занимает этот запрос почти 4 секунды.

[Depleted]

Также очень странно считались NULL, в таблице их несколько тысяч, но данный запрос выдавал 11.

В итоге использовал Ваш подход, немного переделав, родил такое вот чудо:
SELECT q1.c AS men, q2.c AS women, q3.t AS total FROM (SELECT COUNT(DISTINCT CASE WHEN people.gender = 'М' THEN list.id END) AS c FROM ListTable AS list LEFT JOIN PeopleTable AS people ON (list.userID = people.id)) q1, (SELECT COUNT(DISTINCT CASE WHEN people.gender = 'Ж' THEN list.id END) AS c FROM ListTable AS list LEFT JOIN peopleTable AS people ON (list.userID = people.id)) q2, (SELECT COUNT(list.id) AS t FROM ListTable AS list) q3
Таким образом, получил общее число, кол-во мужчин и женщин. А "неопределенных" пользователей уже можно спокойно высчитать.

Ещё раз спасибо большое!

[Константин Цветков]

К сожалению sqlize.online имеет ошибку в движке: читает столбцы по именам, поэтому SELECT 1, 2 выводится не правильно. sqlfiddle.com, например, правильно показывает столбцы, если сервер возвращает их с одинаковыми именами.

[Slava Rozhnev]

Константин Цветков, Поясните пожалуйста в чем разница между sqlfiddle.com и sqlize.online, возможно я смогу исправить. В принципе если есть вопросы по сервису - пишите https://sqlize.online/about

[Slava Rozhnev]

Константин Цветков, вывод sqlfiddle.com:

| 1 | 2 |
|---|---|
| 1 | 2 |

sqlize.online output:

+===+===+
| 1 | 2 |
+===+===+
| 1 | 2 |
+---+---+

[Константин Цветков]

Slava Rozhnev, SELECT 1 AS One, 2 AS One
sqlize.online:

+=====+
| One |
+=====+
| 2   |
+-----+

sqlfiddle.com:

One	One
1	2

SSMS:

One	One
1	2

[Slava Rozhnev]

Константин Цветков, интересный случай! Не думаю что это хороший стиль написания запросов, но я подумаю

[Константин Цветков]

Slava Rozhnev, Для MySQL, MariaDB, SQLite - верно, но PostgreSQL, Microsoft SQL Server 2017 - нет. Последние не дают имена колонкам, если их нет.

(Отсутствует имя столбца)	(Отсутствует имя столбца)
1	2

[Slava Rozhnev]

Константин Цветков, Вы думаете будет лучше выводить индех столбца для PostgreSQL, Microsoft SQL Server ?

[Константин Цветков]

Slava Rozhnev, Если можно по индексу, то выводить и имя столбца, и его значения по нему. Если имя пустое: или показывать индекс, или писать как MS SQL.

[Slava Rozhnev]

Константин Цветков, ОК. Учту Ваши пожелания

[Slava Rozhnev]

Константин Цветков, Спасибо. пофиксил ошибку с безимянными столбцами

Answer 2

[Константин Цветков]

SELECT PeopleTable.gender, COUNT(*) AS GenderCount
  FROM ListTable 
    INNER JOIN PeopleTable ON ListTable.userID = PeopleTable.id
  GROUP BY PeopleTable.gender

А если нужен и ListTable, у которых нет соответствия в PeopleTable

SELECT ISNULL(PeopleTable.gender, '-') [Пол], COUNT(*) [К-во]
  FROM ListTable 
    LEFT JOIN PeopleTable ON ListTable.userID = PeopleTable.id
  GROUP BY ROLLUP (PeopleTable.gender)
UNION
SELECT ' ', COUNT(*)
  FROM ListTable   
ORDER BY 1 DESC, 2

Две последние строчки показывают общее количество. Если они не совпадают выполните проверку. Руководство.

DBCC CHECKTABLE ('PeopleTable')
DBCC CHECKTABLE ('ListTable')

Answer 3

[Rsa97]

LEFT JOIN. Значит вы в обоих случаях учитываете строки из ListTable, для которых нет соответcтвующей записи в PeopleTable.

Comments

[Depleted]

Пробовал просто с JOIN, вывод тот же. Пытался вложенные запросы делать - безрезультатно...

[Depleted]

ChairfaceChippendale, да, но я грешу на кодировку, т.к. если писать like '%М%', ситуация та же, а like N'%М%' не находит ничего.