Mysql получить значение SUM и COUNT из двух таблиц

Question

Это мои столы

Таблица 1: заказы

id  total order_date
1   200    2016-04-22
2   300    2016-04-22

таблица 2: заказ_продукты

 id  order product_id qty
  1    1       1       2
  2    1       2       1
  3    2       2       2
  4    2       1       2

И мой результат должен быть

tot_order total_amount  prd_qty
 2      500             7 

и мой запрос

    SELECT COUNT(ddo.id) AS tot_order, 
           SUM(ddo.total) AS total_amount, 
           (SELECT SUM(dop.qty) 
            FROM  order_products dop 
            WHERE dop.order=ddo.id) AS prd_qty  
    FROM orders ddo 
    WHERE DATE(ddo.`order_date`) BETWEEN '2016-04-22' AND '2016-04-22'

Я могу получить total_order и total_amount, но как получить prd_qty?

Спасибо

Answer 1

Похоже, вы хотите подвести итог order_products в orders а затем подвести итог.

Я хотел бы следовать этой концепции в запросе:

SELECT COUNT(*) tot_order,
       SUM(os.total) total_amount,
       SUM(os.o_prd_qty) prd_qty
  FROM (       
    SELECT o.id,
           o.total,
           SUM(op.qty) o_prd_qty
      FROM orders o
      JOIN order_products op
        ON op.order = o.id
     WHERE o.order_date >= :start_date
       AND o.order_date < :end_date + INTERVAL 1 DAY
  GROUP BY o.id
       ) os

Если вы хотите отдельный orders с их prd_qty просто запустите внутренний запрос.

Я также исправил вашу логику дат, чтобы оптимизатор мог использовать индекс на order_date, Как правило, если вы оберните столбец в функции с одной стороны сравнения, любой индекс в этом столбце будет непригодным для использования.

В качестве примечания я бы очень внимательно посмотрел на ваше соглашение об именах. У каждого есть свои предпочтения, но вы должны быть согласны с этими вопросами:

• сокращайте? - tot, total, prd, product, qty
• присоединять _id для внешних ключей? - order_products.order, order_products.product_id
• включить имя таблицы в столбцы? - orders.total, orders.order_date

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

Лично я вообще не сокращаю, добавляю _id и не включайте имя таблицы в столбцах.

Answer 2

Вы должны использовать подзапрос для вычисления prd_qty:

SELECT SUM(dop.qty) as prd_qty,  dop.order 
FROM  order_products dop 
GROUP BY dop.order

Он считает prd_qty для каждого заказа.
И полный запрос:

SELECT COUNT(ddo.id) AS tot_order, 
       SUM(ddo.total) AS total_amount, 
       sum(op.prd_qty) as prd_qty
FROM   orders ddo 
       JOIN 
       (
            SELECT SUM(dop.qty) as prd_qty,  dop.order 
            FROM  order_products dop 
            GROUP BY dop.order
       ) as op ON (op.order = ddo.id)
WHERE  DATE(ddo.`order_date`) BETWEEN '2016-04-22' AND '2016-04-22'

Answer 3

Этот запрос будет работать для вас.

SELECT COUNT(od.`id`) AS tot_order, SUM(od.`total`) AS total_amount, tablea.prd_qty
FROM orders od JOIN (
                SELECT SUM(op.`prd_qty`) AS prd_qty
                FROM orders od INNER JOIN order_products op
                ON od.`id`=op.`order`
                WHERE DATE(od.`order_date`) BETWEEN '2016-04-22' AND '2016-04-22' 
) tablea
WHERE DATE(od.`order_date`) BETWEEN '2016-04-22' AND '2016-04-22'

Чтобы получить prd_qty, вам придется запустить подзапрос, который я добавил в маленьких скобках после join,

Answer 4

Может быть, вам не хватает GROUP BY пункт

(SELECT SUM(dop.qty) FROM  order_products dop WHERE dop.order=ddo.id GROUP BY dop.order)

Answer 5

Это должно работать нормально для вас.

select sum(order_id) orders, sum(total) total, sum(qty) prd_qty
from (select count(o.id) order_id, sum(o.total) total,
(select sum(qty) from order_products op where o.id = op.`order`) qty
from orders o group by o.id) x;

Довольно простой.

Answer 6

Самый простой запрос, чтобы получить ответы, будет работать как масло через масло.

Однако было бы еще красивее, если бы вы создали индекс для порядка столбцов (id). Если это уже первичный ключ, индексирование столбцов не потребуется. Индексирование выполняется только тогда, когда вы считаете, что ваши данные будут расти в количестве.

select count(o.id) as tot_order ,
sum(o.total) as total_amount,
(select sum(p.qty) from orders o join order_product p on o.id=p.order) as prd_qty     
from orders o;