1

I made a question earlier but since I have deleted it because it was brought to my attention that there was not enough details shown. I am back with details regarding the problem I am having.

The problem is more to do with "HOW" do I construct the query.

Here are some details regarding my problem.

TABLE: orders 
    `orders_id`

TABLE: orders_products
    `orders_products_id`
    `orders_id`
    `products_id`

TABLE: products
    `products_id`
    `manufacturers_id`
    `products_price`
    `products_cost`

TABLE: manufacturers
    `manufacturers_id`

What I need outputting is;

For each manufacturer_id, the sum of all the products_price, the sum of all the products_cost and then the products_profit, derived from them both (aka price - cost = profit).

What I did have was:

SELECT  m.manufacturers_id, m.manufacturers_name,
SUM(p.products_price1)      as 'brand_sales',
SUM(p.products_cost)        as 'brand_cost'

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

LEFT JOIN manufacturers m
ON p.manufacturers_id = m.manufacturers_id

GROUP BY   m.manufacturers_name

And this did, sort, of what I wanted, albeit incorrectly. It did pull back the necessary data but the price, cost and thus the profit fields were populated by values seemingly about 15 times greater than expected and I have no idea what to do about it. My knowledge of SQL is growing but this problem needs a solution soon (and my job is sort of dependant on such a solution being found).

Thanks in advance guys, really really appreciate any adivce/help/comments.

Aleski
  • 1,402
  • 5
  • 16
  • 30
  • 1
    Honestly I still think we might be lacking crucial details to give you a solid answer. Have you examined the data you get out if you complete a select * and remove the group by constraints. Then you will see exactly what data is being collected and how. If this is a huge dataset limit it to a single manufacturer with a where cause temporarily. – ModulusJoe Oct 16 '13 at 17:39
  • I have and like i said, a lot of the orders_products results are repeating. They seem to all be repeated 14 times (15 in total per order) – Aleski Oct 16 '13 at 18:42

1 Answers1

1

Are the table definitions above accurate and complete? It seems you may be missing a few columns (manufacturer's name)?

From what I can see of your requirements, I don't think you really need to use the orders table at all, just the orders_products table.

If there is a one to many relationship between orders and orders_products and you don't need any other column from that table, go ahead and try the query without the orders table:

select  m.manufacturers_name
    ,   sum(p.products_cost) as cost
    ,   sum(p.products_price) as price
    ,   sum(p.products_price)-sum(p.products_cost) as profit
from    order_products op inner join products p on op.products_id = p.products_id
    inner join manufacturers m on p.manufacturers_id = m.manufacturers_id
group by m.manufacturers_name

This should get cost, price, and profit for all of the product records listed in the orders_products table, grouped by manufacturer's name.

If you just want the total cost, price, and profit of essentially one of each product grouped by manufacturer, you'd want to do the whole thing similarly on the products table.

Paul Cauchon
  • 539
  • 1
  • 5
  • 15
  • This seems to be exactly what I wanted. Could you elaborate a bit on why you used the inner join syntax? Super new to that logic. – Aleski Oct 16 '13 at 18:45
  • The inner join gets only those records whose joined value exists in both tables. For example, say your `manufacturers` table has 3 companies: A, B, and C, but you only have products in your `products` table for companies A and B. `select m.name from manufacturers m inner join products p on m.manufacturerID = p.manufacturerID` will return only A and B, because **their manufacturer IDs are present in the products table**. Check this out: http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-join/1353724#1353724 – Paul Cauchon Oct 16 '13 at 19:14