CODEWITHSUNDEEP
mysqlsql
Mathew
Mathew

Reputation: 153

PHP/MySQL Counting project orders

I'm sorry for black boxes, those are the company name

The website sells virtual services with pricing plans and this is the database structure... Projects are the actual services and products are the pricing plans (like Pro,Business,reseller etc.).

The problem is to count the total order count for all projects, but in the order_products table I keep products only, so simple join with grouping by product_id I think does not work for this case. I saw a lot of questions in stackoverflow where people count only the products, but how can I also count Project orders. I can do this with php in business logic part as I think I am able to get product orders count, but I wonder if it is possible to count project orders using MySQL

I'm sorry for black boxes in screenshot, those are the company name. This is how I tried get product orders count.

SELECT 
 p.name,
 COUNT(*) AS db
 FROM order_products AS op
 LEFT JOIN orders AS o ON op.order_id= o.id
 INNER JOIN products AS p ON p.id = op.product_id 
 GROUP BY op.product_id
 ORDER BY COUNT(op.id) DESC

P.S. the project<->product relation is made many to many but in business logic it is made one to many, so we can assume that each product belongs only to one project.

Upvotes: 2

Views: 148

Answers (1)

Anand
Anand

Reputation: 1123

Since there is no quantity field in the orders table, all you need is a simple join:

select pp.project_id, count(*) as project_order_count
from project_product as pp
inner join order_products as op
on op.product_id = pp.product_id
inner join orders as o
on o.id = op.order_id
where o.some_date between '01/12/2017' and '03/10/2017'
group by pp.project_id

Upvotes: 2

Related Questions