MySQL count group by

I have 3 tables, orders(orders_id, date, ...), products (product_id, product_name, product_price) and order_products (product_id, orders_id, product_id, products_quantity) and I need to group the produ开发者_StackOverflow社区cts so that they are displayed by product and total quantity per product to make it easier for the eshop manager to know how many items per product have been ordered.

I'm having a little bot of trouble thinking of the correct sql syntax, I keep bumping into group by issues and i'd like some help.

This is what I've done so far

select *, op.products_quantity as pquant, count(*) as `count`
from orders o
left join orders_products op on o.orders_id = op.orders_id
left join products p on op.products_id = p.products_id 
group by op.orders_products_id
order by op.products_id desc;

Looking at what you have, you're counting orders, not summing the quantity of the orders..

So

if you had

orders

monday 5 potatoes
tuesday 2 carrots
wednesday 3 potatoes

You wanted

potatoes 8
carrots 2

in which case you'd want to do

select sum(quantity),item from orders group by item

I didnt quite see what the differende between orders_products and orders was.

Maybe a bit of sample data would help?

Select p.Product_name,sum(prodcuts_quantity) as OrderedQuantity from 
Order_products op join
Products p on p.Product_id = op.product_id
group by p.Product_name

If you need Total quantity and total orders per product than you can do that in following way

SELECT p.*,op.total_order,op.total_quantity FROM PRODUCT LEFT JOIN (SELECT COUNT(*) AS total_order, SUM(quantity) AS total_quantity FROM orders_products GROUP BY product_id) AS op ON p.id = op.product_id

This should work, use SUM, not COUNT:

SELECT
    *,
    SUM(op.products_quantity) 
FROM
    orders AS o
    LEFT JOIN orders_products AS op ON o.orders_id  = op.orders_id 
    LEFT JOIN products AS p ON op.products_id = p.products_id   
GROUP BY p.products_id 
ORDER BY p.products_id  DESC