Mysql get SUM and COUNT value from two tables

Question

This is my tables

table 1: orders

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

table 2: order_products

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

And my result should be

tot_order total_amount  prd_qty
 2      500             7 

and my query is

    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'

I can get the total_order and total_amount, but how to get prd_qty?

Thanks

Answer 1

Maybe you are missing the GROUP BY clause

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

Answer 2

You should use subquery for calculating prd_qty:

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

It counts prd_qty for each order.
And the full query:

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

This query will work for you.

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'

To get the prd_qty, you will have to run a subquery I added in the small brackets after join.

Answer 4

Looks like you want to sum up the order_products into orders and then sum up the result.

I would follow this concept in the query:

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

If you want the separate orders with their prd_qty just run the inner query.

I have also fixed up your date logic so that the optimiser can use an index on order_date. Generally, if you wrap a column in a function on one side of a comparison any index on that column is unusable.

As a side note, I would take a long hard look at your naming convention. Everyone has their preferences but you should be consistent with these questions:

• abbreviate? - tot, total, prd, product, qty
• append _id for foreign keys? - order_products.order, order_products.product_id
• include table name in columns? - orders.total, orders.order_date

Following a convention makes your database much easier to navigate and work with. Otherwise it's back to the schema when writing every query!

Personally I generally don't abbreviate, do append the _id and don't include the table name in columns.

Answer 5

The Simplest query to get the answers would be, this will work as a knife through the butter.

However it would even more beautiful if you create index for the column orders(id). If it is already a primary key column indexing won't be needed Indexing is only when you believe your data will grow higher in number

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;

Answer 6

This should work fine for you.

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;

Fairly simple.