Vendor Invoice History Table Add-on Dev (COGS by invoice)

so can anyone help me out w/ an sql statement?

I've added products_cost and products_invoice to the products table. I want to figure out the cost of the sold products grouped by invoice name. (products_invoice would be something like "023014Distributor003" and have 30 or so products on it; not very atomized I know)

This statement seems to run forever in phpMyAdmin

SELECT p.products_id, p.products_invoice, sum(p.products_cost)
FROM products p, orders_products op
WHERE op.products_quantity>0
GROUP BY p.products_invoice

seriously stuck on this one... thanks.

wolfderby:

so can anyone help me out w/ an sql statement?

I've added products_cost and products_invoice to the products table. I want to figure out the cost of the sold products grouped by invoice name. (products_invoice would be something like "023014Distributor003" and have 30 or so products on it; not very atomized I know)

This statement seems to run forever in phpMyAdmin

SELECT p.products_id, p.products_invoice, sum(p.products_cost)
FROM products p, orders_products op
WHERE op.products_quantity>0
GROUP BY p.products_invoice

> 
> seriously stuck on this one... thanks.
How about "connecting" the products and orders_products tables:

SELECT p.products_id, p.products_invoice, sum(p.products_cost)
FROM products p, orders_products op
[B]WHERE op.products_id = p.products_id
AND[/B] op.products_quantity>0
GROUP BY p.products_invoice

wolf,
i rarely get into people's business operations. i'm making an exception here, just to make some observations.

your change of the products table is very limiting and could destroy a potential audit trail. you are limiting yourself to 1 cost/product as well as 1 invoice/purchase per product.

costing is much more complex than i will get into here. but if you purchase 100 units of product A, it becomes a hot seller, and you want to repurchase it, what happens then? and then lets say the cost has gone up by 12%? how would you keep track of that? your only solution would be to create a new product with the new invoice. but what happens if you then want to find out how many of product A you sold? you have 2 product numbers that you need to keep track of as they are the same product.

perhaps this situation does not apply to you, but maintaining separate tables for vendor purchases is a much better way to handle these things. and then add the product cost to the order_products table. just my 2 cents.

the problem with the sql statement the way it is currently written is two-fold: products_id has no significance, and there is no accounting for the quantity sold on each order. if i wanted to just find out the cost for each invoice of products sold from that invoice, i would use:

SELECT p.products_invoice, sum(p.products_cost *op.products_quantity), sum(p.products_cost), count(op.products_quantity), sum(op.products_quantity)
FROM  orders_products op
left join products p on op.products_id = p.products_id
where op.products_quantity>0  
GROUP BY p.products_invoice

i have included a couple of other numbers to show the potential problems if run as you had constructed. the key number is the sum(p.products_cost *op.products_quantity).

if i wanted to see the cost of each item that has a sale for every invoice, i would modify the sql as such:

SELECT p.products_invoice, p.products_id, sum(p.products_cost *op.products_quantity), sum(p.products_cost), count(op.products_quantity), sum(op.products_quantity)
FROM  orders_products op
left join products p on op.products_id = p.products_id
where op.products_quantity>0  
GROUP BY p.products_invoice, p.products_id

best.

carlwhat:

wolf,
i rarely get into people's business operations. i'm making an exception here, just to make some observations.

your change of the products table is very limiting and could destroy a potential audit trail. you are limiting yourself to 1 cost/product as well as 1 invoice/purchase per product.

costing is much more complex than i will get into here. but if you purchase 100 units of product A, it becomes a hot seller, and you want to repurchase it, what happens then? and then lets say the cost has gone up by 12%? how would you keep track of that? your only solution would be to create a new product with the new invoice. but what happens if you then want to find out how many of product A you sold? you have 2 product numbers that you need to keep track of as they are the same product.

perhaps this situation does not apply to you, but maintaining separate tables for vendor purchases is a much better way to handle these things. and then add the product cost to the order_products table. just my 2 cents.

the problem with the sql statement the way it is currently written is two-fold: products_id has no significance, and there is no accounting for the quantity sold on each order. if i wanted to just find out the cost for each invoice of products sold from that invoice, i would use:

SELECT p.products_invoice, sum(p.products_cost *op.products_quantity), sum(p.products_cost), count(op.products_quantity), sum(op.products_quantity)
FROM orders_products op
left join products p on op.products_id = p.products_id
where op.products_quantity>0
GROUP BY p.products_invoice

> 
> i have included a couple of other numbers to show the potential problems if run as you had constructed.  the key number is the sum(p.products_cost *op.products_quantity).
> 
> if i wanted to see the cost of each item that has a sale for every invoice, i would modify the sql as such:
> 
> ```
SELECT p.products_invoice, p.products_id, sum(p.products_cost *op.products_quantity), sum(p.products_cost), count(op.products_quantity), sum(op.products_quantity)
FROM  orders_products op
left join products p on op.products_id = p.products_id
where op.products_quantity>0  
GROUP BY p.products_invoice, p.products_id

best.

Thanks, yeah I should have mentioned I created a "re-order id" aka reoid # that is a 3 digit integer for each time I repurchase a product, I try to increment it around wholesaler_sku and distributor_sku fields w/ this productmachine script that outputs easypopulate csv's from 4 or 5 column basic invoices. (quan, desc, cost, price, (sku-optional)

The productmachine database has sizedata tables, category tables, etc. I think it'd be too customized to share atm, and it usually only gets me half way.

So TESTPRODUCT-001 becomes TESTPRODUCT-002

Generally I'm trying to make it more of a goal to contribute to this community (some/more) if I can learn how. :)

so I'm trying to build an $invoice_totals as an associative array

and then add the invoice_cogs into an associative array field, but after the second query, within the while loop if I try to add it back in like...

'newsoldsum' => $invoices_totals[$invoicename]['cogs_sum'];

it's not matching the value back up w/ the array $invoices_totals[$invoicename] part.

perhaps I should have tried making a class/object?

what exactly are you trying to accomplish?

So I ended up figuring it out somewhat using array_merge after 2 queries with while loops building 2 arrays then merging them under the invoice name.

Usually before I import/easypopulate my orders into my website, I incorporate shipping costs based on a weighted-by-cost method.

Basically I'm working on a table of invoices.

Invoice Date | Invoice Name | Cost of Products w/ Quan > 0 (in products table) | Invoice COGS | Invoice Total