Здравствуйте. Имеется запрос, который должен выводить счета, попутно считая общую сумму по всем наименованиям и оплаченную сумму.

Например, имеем 2 счета:
1) Счет №1 (2 наименования на общую сумму 2000 руб.), был 1 платеж на всю сумму.
2) Счет №2 (1 наименование на сумму 500 руб.), по ошибке было 2 платежа по 500 руб.

Должно вывести так:
Счет №1 | 2 услуги | 1 платеж | 2000 руб. (сумма счета) | 2000 руб. (оплачено)
Счет №2 | 1 услуга | 2 платежа | 500 руб.   (сумма счета) | 1000 руб. (оплачено)

А сейчас выводит так:
Счет №1 | 2 услуги | 1 платеж | 2000 руб. (сумма счета) | 4000 руб. (оплачено)
Счет №2 | 1 услуга | 2 платежа | 500 руб.  (сумма счета)  | 1000 руб. (оплачено)

Ошибка в том, что он почему то количество услуг (наименований) умножил на сумму счета.

Запрос:


Либо так:
Счет №1 | 2 услуги | 1 платеж | 2000 руб. (сумма счета) | 2000 руб. (оплачено)
Счет №2 | 1 услуга | 2 платежа | 500 руб.   (сумма счета) | 500 руб. (оплачено)

Если добавить еще один DISTINCT, то теперь он не учитывает 2 платежа по счету.

Запрос:


Упрощенная структура базы данных выглядит так:


Это сообщение отредактировал(а) JEEN - 18.3.2016, 01:09

Попробовал с подзапросами сделать, результат на первый взгляд верный, но может быть возможно сделать это в один запрос?



Это сообщение отредактировал(а) JEEN - 18.3.2016, 01:43

Ну это ТВОЯ ошибка. Которая проистекает из непонимания того, как выполняется JOIN. У тебя две записи в payments и две в details - в результате после JOIN ты получишь четыре объединённых записи. Отсюда и кратное увеличение суммы.


Интересно, как это он дал верный результат БЕЗ ГРУППИРОВКИ?

Вообще решение с подзапросами верное, но скорее всего медленное - не всегда коррелированные подзапросы обрабатываются правильно, зачастую они выполняются для каждой записи основной таблицы, что есть неправильно.

Более адекватным будет запрос типа (пишу от руки, могу накосячить)

Да большынство СУБД ужэ научилось всё это правильно преобразовывать в единый план. Более того, шансы оптимизатору ошыбиться как раз большэ при наличии группировок во вложэнных запросах, из-за которых он не можэт произвольно менять начальную таблицу.

Впрочем, при LEFT JOin это так и так ему сложно.

В итоге -- тривиальный план с проходом по таблицэ invoices и индэксной выборкой из payments и details будет правильным чуть менее чем всегда, так что за скорость особенно можно не волноваться.

К сожалению, ТС не озвучил СУБД, не говоря уж о версии. 

Да, за такое использование DISTINCT надо сразу расстреливать по-моему.

Разные значения стоимости позицыи вообще никак не относятся к разным позицыям. Ну, то есть у одной позицыи стоимость будет одинакова, конечно, но вот у разных она тожэ можэт быть одинаковой.

Правильное приближэние -- поделить sum() на количество "неправильных" повторений, т.е. количество записей из другой таблицы, которые были использованы в данной группе. В частности, sum(d.cost*d.amount) / count(DISTINCT p.id), и sum(p.sum) / count(DISTINCT d.id). Единственно, возникнет проблема с LEFT JOIN -- когда записей в другой таблицэ нет, но ведь одно повторение осталось! Так что потребуется вместо count(DISTINCT p.id) взять что-нибудь вроде count(DISTINCT COALESCE(p.id, -1)) чтобы всегда NULL было заменено на (невозможное в других случаях) значение, и, таким образом, это повторение данных посчиталось, ну или написать CASE для замены 0 на 1: CASE WHEN count(DISTINCT p.id) = 0 THEN 1 ELSE count(DISTINCT p.id) END. И, естественно, тожэ самое для другой таблицы.

Добавлено через 3 минуты и 31 секунду


MySQL, надо думать, кто ещё позволит в SELECT указать колонку, которой в GROUP BY не было?

Всем спасибо за помощь. Вчера еще перенес свой код на рабочий проект, работает он без группировки отлично, точно так, как я написал во втором сообщении.
БД MySQL 5.5.47

tzirechnoy, вот видишь... а ты 

Вот когда он до 5,7 обновится - тогда ещё на что-то можно будет надеяться.