Возможные причины (кратко):
- NULL в country: строки с неизвестной страной дают группу с NULL (и при отсутствии заказов $SUM$ вернёт NULL, не $0$).
- Дублирующиеся ключи в users (несколько строк с одинаковым user_id): каждое совпадение умножает соединение — заказы считаются несколько раз.
- Дубли в orders (повторные записи заказа) — завышение сумм.
- Несовпадающие типы/форматы ключей (например integer vs text, пробелы, ведущие нули, разный регистр) — часть связей не совпадает или требует преобразования.
- JOIN по неверному столбцу или непреднамеренное расширение результата (например users не уникален по user_id, поэтому LEFT JOIN порождает дубликаты пользователей на одну страну).
- Нормализация country (разные варианты «US», «us», «U.S.»/пробелы) — множественные строки для одной страны.
Диагностика (выполнить и посмотреть результаты):
- Есть ли NULL в country:
SELECT country, COUNT(*) FROM users GROUP BY country;
(проверьте значение $COUNT(\ast )$)
- Повторяющиеся user_id в users:
SELECT user_id, COUNT(*) FROM users GROUP BY user_id HAVING COUNT(*) > 1;
- Повторяющиеся order_id / полные дубли в orders:
SELECT order_id, COUNT(*) FROM orders GROUP BY order_id HAVING COUNT(*) > 1;
SELECT user_id, amount, COUNT(*) FROM orders GROUP BY user_id, amount HAVING COUNT(*) > 1;
- Заказы с NULL user_id:
SELECT COUNT(*) FROM orders WHERE user_id IS NULL;
- Проверить типы колонок:
SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name IN ('users','orders');
- Посмотреть, сколько заказов/строк присоединяется к каждому пользователю (поможет увидеть мультипликацию):
SELECT u.user_id, u.country, COUNT(o.order_id) AS orders_cnt
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.country
ORDER BY orders_cnt DESC
LIMIT 50;
- Нормализация country — показать варианты:
SELECT country, COUNT(*) FROM users GROUP BY country ORDER BY COUNT(*) DESC LIMIT 50;
Корректные варианты запросов (чтобы избежать дубликатов и NULL-результатов):
1) Аггрегировать по пользователю сначала, затем по стране (устраняет мультипликацию при дублирующихся users):
SELECT u.country,
COALESCE(SUM(uo.total), 0) AS total_by_country
FROM users u
LEFT JOIN (
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
) uo ON u.user_id = uo.user_id
GROUP BY u.country;
(здесь агрегат по пользователю — $SUM(amount)$, итог по стране — $COALESCE(SUM(uo.total),0)$)
2) Использовать уникальную проекцию пользователей (если в users есть дубли записей одного user_id):
SELECT u.country, SUM(o.amount) AS total_by_country
FROM (SELECT DISTINCT user_id, country FROM users) u
JOIN orders o ON u.user_id = o.user_id
GROUP BY u.country;
(если нужны страны пользователей без заказов — заменить JOIN на LEFT JOIN и обернуть суммирование в COALESCE)
3) Если проблема — типы/форматы ключей, привести к общему типу при соединении:
-- пример для PostgreSQL, если users.user_id integer, orders.user_id text:
SELECT u.country, SUM(o.amount) FROM users u
LEFT JOIN orders o ON CAST(u.user_id AS TEXT) = o.user_id
GROUP BY u.country;
4) Нормализация country перед группировкой (убрать регистр и пробелы):
SELECT TRIM(UPPER(country)) AS country_norm, SUM(amount)
FROM users u
LEFT JOIN orders o USING (user_id)
GROUP BY TRIM(UPPER(country));
5) Чтобы явно показать отсутствие заказов как $0$, используйте COALESCE:
SELECT u.country, COALESCE(SUM(o.amount), 0) FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.country;
Резюме: сначала диагностируйте NULL/дубли/типы приведёнными запросами; затем либо агрегируйте заказы по user_id в подзапросе и уже это соединяйте с пользователями, либо избавьтесь от дублей в users (DISTINCT или очистка данных), приведите типы/форматы ключей и используйте COALESCE для вывода $0$ вместо NULL.