CODEWITHSUNDEEP
phpsqljoin
Marek Kaliszuk
Marek Kaliszuk

Reputation: 577

Join multiple tables and sum results for each row in first table

I have a problem with creating efficient sql query for shop statistics. I have three tables: categories, product_categories and orders_products.

Table categories - contains list of categories and structure looks like(short version):

id
name

Table product_categories - contains connections between products and categories. Structure in short version:

id
product_id
category_id

Table orders_products - contains all products what were sold in shop. Structure below:

id
product_id
price
quantity

What I want to do is display table with category name and value of sold items for current category.

Problem occurs when i have over 2000 categories, more than 20 items in each and many sold items.

I tried:

1) Get all categories in one simple query

2) Foreach them(in php) and use join for products_catogories and orders_products

SELECT orders_products.id,orders_products.product_price_gross,orders_products.product_qty,orders_products.option_price,orders_products.rebate
          FROM product_categories
          LEFT JOIN orders_products
          ON product_categories.product_id = orders_products.product_id

3) Foreach result in php and sum values

4) Display results

5) Effects - hangs db when is more that 50 categories in foreach.

I have no idea how optimize query for that much data. I hope You could help me with that because I'm not so good with joins.

Kind regards Mark

Upvotes: 1

Views: 1577

Answers (2)

Daniel
Daniel

Reputation: 3514

You might do all thsi stuff in a single step, letting MySQL perform those calculations for you:

SELECT categories.name,
       SUM(order_products.price * order_products.quantity)
FROM order_products
LEFT JOIN product_categories ON product_categories.product_id = order_products.product_id
LEFT JOIN categories ON product_categories.category_id = categories.id
GROUP BY categories.name,
         categories.id

This will only return the sum for categories, which contain at least a single purchase. See this example sqlfiddle.

UPDATE

I further developed the SQL query (new fiddle) to include quantity per category and add "empty" categories without sales to the reult by turning the JoOIN around:

SELECT categories.name,
       Ifnull(Sum(order_products.quantity), 0) AS quantity,
       Ifnull(Sum(order_products.price * order_products.quantity), 0) AS `Total Sales`
FROM categories
LEFT JOIN product_categories ON product_categories.category_id = categories.id
LEFT JOIN order_products ON product_categories.product_id = order_products.product_id
GROUP BY categories.name,
         categories.id

Upvotes: 1

Priyank
Priyank

Reputation: 1009

I am not sure but you can try this,

SELECT orders_products.id,sum(orders_products.product_price_gross),sum(orders_products.product_qty),sum(orders_products.option_price),sum(orders_products.rebate)
          FROM product_categories
          LEFT JOIN orders_products
          ON product_categories.product_id = orders_products.product_id group by product_categories.category_id

Upvotes: 0

Related Questions