Как я могу использовать SUM и COUNT в одном запросе с mysql и получить точные результаты

У меня есть две таблицы в отношениях один ко многим. Точнее, t1 — это информация о заказе, а t2 — это детализация позиций по этим заказам.

Я пытаюсь использовать такой запрос:

   SELECT COUNT(DISTINCT(t1.id)) order_count,
          SUM(t1.order_total) order_total,
          SUM(t2.product_price) product_total,
          DATE(t1.order_date) order_date
     FROM t1
LEFT JOIN t2 ON t1.id = t2.id
 GROUP BY t1.order_date

Запрос возвращает правильное значение для order_count. Однако другие значения завышены неправильно. Я понимаю, что с левым соединением я добавляю лишние строки, и поэтому сумма неверна. Я просто не знаю, как это исправить.

Любая помощь будет принята с благодарностью.

РЕДАКТИРОВАТЬ: вывод должен быть примерно таким:

ДАТА | КОЛИЧЕСТВО ЗАКАЗОВ | ОБЩИЙ ИТОГ

Я разработал запрос ниже на основе ответа. Он возвращает все значения правильно, за исключением «coupon_total», который он каждый раз возвращает как 0.

SELECT
COUNT(DISTINCT(o.order_number)) order_count,
DATE(o.order_date) order_date,
SUM(o.total_product_total) product_total,
SUM(o.total_shipping) shipping_total,
SUM(o.total_grand_total) grand_total,
o.coupon_total
FROM (
 SELECT
 DATE(o.order_date) order_date,
 o.order_number,
 o.total_product_total,
 o.total_shipping,
 o.total_grand_total,
 IF(op.record_type='cpn',SUM(op.price),0) coupon_total
 FROM orders o
 LEFT JOIN orders_products op ON o.order_number=op.order_number
 GROUP BY o.order_number
) o
GROUP BY DATE(o.order_date)
ORDER BY o.order_date DESC

mysql sql join aggregate-functions
person JungAtHeart    schedule 06.11.2010    source источник
comment
Почему вы группируете по дате, но не выбираете ее? Непонятно, что вы хотите, чтобы этот запрос возвращал!   -  person Dan Grossman    schedule 07.11.2010

Ответы (1)


arrow_upward
4
arrow_downward

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

SELECT t1.id, t1.order_total, SUM(t2.product_price) product_total
FROM t1 LEFT JOIN t2 on t1.id=t2.id 
GROUP BY t1.id, t1.order_total

этот запрос возвращает все отдельные заказы с их суммированной ценой продукта.

Сделайте внешний запрос, суммирующий order_total и возвращающий счет, например

SELECT COUNT(DISTINCT(t1.id)) order_count,
       SUM(t1.order_total) order_total,
       SUM(t2.product_price) product_total
FROM (
  SELECT t1.order_date, t1.id, t1.order_total, SUM(t2.product_price) product_total
  FROM t1 LEFT JOIN t2 on t1.id=t2.id 
  GROUP BY t1.order_date, t1.id, t1.order_total
) GROUP BY t1.order_date

Нет гарантии, что этот код действительно работает, мой SQL немного заржавел... Но я надеюсь, что вы поняли идею.

ИЗМЕНИТЬ (в ответ на ваши правки...)

Конструкция IF неуместна в вашем коде: либо вы используете что-то вроде SUM(IF(op.record_type='cpn',op.price,0)), либо, что еще лучше, помещаете предложение WHERE во внутренний запрос, выбирая только OP с record_type='cpn', т.е.

....
SUM(op.price) coupon_total
FROM orders o
LEFT JOIN orders_products op ON o.order_number=op.order_number
WHERE op.record_type='cpn'` 
GROUP BY o.order_number
....
person MartinStettner    schedule 06.11.2010
comment
Я мог бы реализовать это, но я не решаюсь использовать его, потому что я не понимаю, как он работает и что именно он делает, и как изменить/исправить его в будущем, если с ним что-то пойдет не так. - person JungAtHeart; 07.11.2010
comment
Думайте о первом (под-) запросе как о промежуточной таблице, которая содержит все заказы и их соответствующие итоги продуктов (т.е. суммирование только по t2). Затем второй (внешний) запрос использует это и вычисляет оставшиеся суммы/счетчики. Поскольку для каждого заказа в подзапросе есть только одна строка, сумма по order_total должна быть выполнена правильно. - person MartinStettner; 07.11.2010
comment
Я реализовал запрос, аналогичный вашему. Результаты почти точные. Правки в моем исходном вопросе объясняют больше. - person JungAtHeart; 07.11.2010