Краткий ответ

DISTINCT внутри SUM исключает повторяющиеся значения, а не повторяющиеся заказы. Поэтому одинаковые суммы разных заказов будут учтены только один раз, и итог окажется заниженным.

Чтобы посчитать выручку уникальных заказов, сначала нужно устранить дубли на уровне идентификатора заказа, а затем применить обычный SUM.

Исторический контекст

Агрегаты с DISTINCT появились как способ вычислять показатель по множеству уникальных значений: например, сумму различных тарифов или количество различных категорий. Такой оператор работает над набором значений конкретного выражения, а не понимает бизнес-сущность строки.

SQL не может самостоятельно догадаться, что заказ определяется идентификатором, а сумма является лишь его атрибутом. Если требуется уникальность по заказу, её нужно явно выразить через идентификатор заказа или предварительное группирование.

Постановка проблемы

Пусть два разных заказа имеют сумму 100, а третий — 50. Выручка равна 250, но SUM(DISTINCT amount) обработает множество значений 100 и 50 и вернёт 150.

Такая ошибка особенно часто возникает после соединения заказов с позициями: один заказ появляется в результате несколько раз. Однако замена обычной суммы на SUM(DISTINCT сумма) не является универсальным исправлением. Она одновременно удаляет повторы разных заказов с одинаковой суммой.

Подробное решение

DISTINCT внутри агрегата применяется к результатам его аргумента. Для выражения amount набором будут именно значения суммы: одинаковые числа схлопываются независимо от того, из каких строк они получены.

Правильный алгоритм состоит из двух логических шагов:

получить ровно одну строку на заказ;
просуммировать сумму уже этого набора.

Минимальный пример:

WITH source(order_id, amount) AS (
    VALUES (1, 100), (1, 100), (2, 100), (3, 50)
), per_order AS (
    SELECT order_id, MAX(amount) AS amount
    FROM source
    GROUP BY order_id
)
SELECT SUM(amount) AS revenue
FROM per_order;

В примере повтор заказа с идентификатором 1 устраняется при группировке. Заказы 1 и 2 сохраняются отдельно, несмотря на одинаковую сумму, поэтому результат равен 250. MAX здесь допустим только при условии, что сумма заказа одинакова во всех его дублированных строках; иначе нужно сначала исправить логику соединения или выбрать источник с корректной гранулярностью.

Если исходная таблица уже содержит одну строку на заказ, никакой DISTINCT внутри SUM не нужен: следует суммировать столбец напрямую. Если дубли появились из-за соединения, надёжнее предварительно агрегировать таблицу позиций до уровня заказа, а затем соединять её с заказами.

Следует также учитывать, что SUM(DISTINCT ...) игнорирует NULL, как и обычный SUM. Кроме того, уникальность определяется значением всего выражения: при суммировании выражения с несколькими компонентами нужно отдельно проверить, не теряются ли разные бизнес-сущности с одинаковым результатом выражения.

Ситуация из практики

В отчёте по клиентам заказ присоединяли к таблице позиций. Заказ на 100 рублей с тремя позициями превращался в три строки, поэтому обычная сумма завышала выручку втрое. Команда заменила её на SUM(DISTINCT amount), но обнаружила, что клиенты с несколькими заказами по 100 рублей теперь недосчитаны.

Рассматривались два варианта. SUM(DISTINCT amount) был простым, но логически неверным: он различал суммы, а не заказы. Устранение дублей через SELECT DISTINCT order_id, amount было лучше, но требовало гарантии, что сумма постоянна для каждого заказа.

Выбрали предварительную агрегацию позиций до одной строки на order_id, после чего присоединили результат к заказам и применили обычный SUM. Это сохранило все отдельные заказы, устранило размножение строк и сделало гранулярность каждого этапа явной.

Что кандидаты часто упускают

Чем отличается SUM(DISTINCT amount) от устранения дублей по order_id?

Первый вариант оставляет по одному экземпляру каждого числового значения суммы. Второй оставляет по одной строке каждой сущности, определённой идентификатором заказа. Это разные множества: два заказа могут иметь одну сумму, но должны оба войти в выручку.
Когда SUM(DISTINCT amount) всё же может быть корректным?

Только когда бизнес-показатель действительно определяется уникальными значениями суммы, а не строками или заказами. Например, это может быть сумма различных тарифов в справочном наборе, если повторение одного тарифа не должно увеличивать результат. Для выручки заказов такое предположение обычно неверно.
Почему устранение дублей после соединения не всегда достаточно выполнить через DISTINCT по двум столбцам?

DISTINCT order_id, amount корректен лишь тогда, когда одному заказу соответствует одно неизменное значение суммы. Если в дублированных строках сумма различается из-за ошибки данных, валюты, скидки или разной гранулярности, такой шаг оставит несколько строк на заказ. В этом случае нужно определить правило формирования суммы заказа и агрегировать данные по его исходным компонентам.