Туплю. Помогите сделать выборку (суммирование значиений из N таблиц)

[simplyAl]

Туплю. Помогите сделать выборку (суммирование значиений из N таблиц)

В общем, все вроде тривиально, но 4й час уже бьюсь - не получается.

Есть таблица "клиенты" (customers), и есть еще 3 таблицы:
1) покупки (поля: id, customer_id, purchase_amount)
2) инвойсы (поля: id, customer_id, invoice_amount)
3) отправления (поля: id, customer_id, delivery_duration)

Таблицы связаны с покупателем по полю customer_id, т.е. вроде как one-to-many. В каждой из 3 таблиц может быть несколько строчек для одного customer_id, кол-во записей для каждого покупателя в 3 таблицах разное (скажем покупатель 1 может иметь 5 инвойсов и 1 покупку, покупатель 2 - 0 инвойсов и 10 покупок).

Собсно задача. Одним запросом нужно получить список всех клиентов и для каждого:
SUM(purchase_amount)
SUM(invoice_amount)
AVG(delivery_duration)

Пробовал подзапросами и тремя join'ами - не получается...

 

[Gas]

Ну дык 3 джойна + group by по id кастомера

 

[simplyAl]

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

SELECT *, SUM(sales.amount), SUM(invoices.amount), AVG(deliveries.duration)
FROM customers
LEFT JOIN sales ON customer_id=customers.id
LEFT JOIN invoices ON customer_id=customers.id
LEFT JOIN deliveries ON customer_id=customers.id
GROUP BY customers.id


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

 

[asm]

simplyAl
запрос в студию

 

[simplyAl]

asm
см выше, подправил пост

-~{}~ 16.07.07 19:08:

Вот что получается если к кастомерам джойнить инвойсы и продажы:

customers.id....invoices.id.....invoices.amount.....sales.id.....sales.amount
------------------------------------------------------------------------------------
.....1.......................1....................10.......................1....................110
.....1.......................1....................10.......................2....................90
.....1.......................2....................50.......................1....................110
.....1.......................2....................50.......................2....................90

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

в идеале надо чтобы получалось:

customers.id....invoices.id.....invoices.amount.....sales.id.....sales.amount
------------------------------------------------------------------------------------
.....1.......................1....................10.......................1....................110
.....1...................NULL..................NULL...................2....................90
.....1.......................2....................50.......................NULL..............NULL
.....1....................NULL.................NULL...................NULL...................NULL

ну или как угодно по-другому, лишь бы строчки не дублировались

 

[Gas]

Исключительно в качестве извращения (не пытайтесь повторить это дома

SUM(sales.amount) заменяем на
sum(sales.amount)/(if(count(distinct invoices.id),count(distinct invoices.id),1)*if(count(distinct deliveries.id),count(distinct deliveries.id),1))

с SUM(invoices.amount) то-же, только таблицы sales и deliveries

А чего с подзапросами не получается? там-то проблем таких нет, но вот насчёт скорости нужно смотреть.

 

[simplyAl]

Gas
с подзапросами не получается, т.к. они не могут быть коррелированными когда используются во FROM

Например,

SELECT customers.*, report.total
FROM customers,

(SELECT SUM(invoices.total) AS total
FROM invoices
WHERE invoices.customer_id=customers.id
) AS report

выдает ошибку "неизвестное поле customers.id в подзапросе", в результате я не могу связать кастомеров и инвойсы....

 

[Gas]

так тебе нужно подзапрос не во from вызывать, а в результате, как одно из полей
[SQL]
SELECT customers.*, AVG(deliveries.duration),
(SELECT SUM(purchase_amount) FROM sales WHERE customer_id=customers.id) as p_amm,
(SELECT SUM( invoice_amount) FROM invoices WHERE customer_id=customers.id) as i_amm
FROM customers
LEFT JOIN deliveries ON customer_id=customers.id
GROUP BY customers.id
[/SQL]

 

[simplyAl]

Gas
Нифига я протупил
Заработало Спасибо!