Группировка строк с двух LEFT JOIN таблиц без подзапроса?

Question

[Александр]

Добрый вечер уважаемое сообщество! Допустим есть такая база:

Таблица users
|_______ id
|_______ name

Таблица orders
|_______ id
|_______ user_id
|_______ amount

Таблица order_items
|_______ id
|_______ order_id
|_______ product_id
|_______ qty

Задача стоит следующая - без подзапросов для всех пользователей вывести общую сумму заказов и общее кол-во купленных товаров (т.е. SUM(order_items.qty)).

Для первого условия всё просто:

SELECT 
    u.name, 
    IFNULL(SUM(o.amount), 0) AS full_amount
FROM 
    `users` u
LEFT JOIN 
    `orders` o ON u.id = o.user_id
GROUP BY 
    u.id

Для полного решения должно быть что-то такое:

SELECT 
    u.name,
    IFNULL(SUM(o.amount), 0) AS full_amount,
    IFNULL(SUM(i.qty), 0) AS full_quantity
FROM 
    `users` u
LEFT JOIN 
    `orders` o ON u.id = o.user_id
LEFT JOIN 
    `order_items` i ON i.order_id = o.id
GROUP BY 
    u.id

В таком варианте full_quantity считается правильно, а full_amount нет, т.к., насколько я понимаю, таблица order_items не группируется при подсчёте, и выдаёт строки с одинаковыми данными из orders но разными из order_items.

Собственно вопрос - какие есть варианты корректного решения данной задачи?
Заранее благодарен!

P.S.: С профессиональным праздником! :)

Comments

[idShura]

Реализовать то что ты хочешь без подзапросов не получиться, потому что в MySQL нет аналитических функций. А чем собственно не устраивают подзапросы?

[Александр]

idShura: Окей, в SELECT добавили

(SELECT IFNULL(SUM(i.qty), 0) FROM `orders` o INNER JOIN `order_items` i ON o.id = i.order_id WHERE o.user_id = u.id) AS full_quantity

и всё работает как нужно.

Но в моём конкретном случае у меня всё равно идёт LEFT JOIN `orders` с кучей фильтров(условий), и его никак нельзя использовать повторно (для full_quantity), и нужно в этот подзапрос писать эти же условия. Именно из-за этого и хотел найти возможное решение, чтоб немного сократить время выполнения запроса.

[idShura]

Александр: Вот так попробуй:

SELECT u.name,
id,
IFNULL(SUM(o.amount), 0) AS full_amount,
(SELECT FNULL(SUM(i.qty), 0) FROM `order_items` i where i.order_id = o.id ) full_quantity
FROM `users` u
LEFT JOIN `orders` o ON u.id = o.user_id
--Where кучей фильтров(условий)
GROUP BY u.id

[Александр]

idShura: так пробовал - не хочет группировать SUM(i.qty), выводит только значение одной строки

[idShura]

Александр: исправил, пробуй

SELECT u.name,
id,
IFNULL(SUM(o.amount), 0) AS full_amount,
SUM((SELECT FNULL(SUM(i.qty), 0) FROM `order_items` i where i.order_id = o.id )) full_quantity
FROM `users` u
LEFT JOIN `orders` o ON u.id = o.user_id
--Where кучей фильтров(условий)
GROUP BY u.id

[Александр]

idShura: Блин, это реально работает :) Спасибо!

Answer 1

[idShura]

Последний вариант оформил в виде ответа.

SELECT u.id,
             u.name, 
             IFNULL(SUM(o.amount), 0) AS full_amount,
             SUM((SELECT FNULL(SUM(i.qty), 0) FROM `order_items` i where i.order_id = o.id )) full_quantity
FROM `users` u
LEFT JOIN `orders` o ON u.id = o.user_id
--Where кучей фильтров(условий)
GROUP BY u.id, u.name

Comments

[Александр]

С подзапросом, но абсолютно удовлетворительное решение в моём случае. Т.к. главной задачей было избежать "--Where с кучей фильтров(условий)" в подзапросе.